☰

日付形式を変換する Excel テキスト

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
日付形式を変換する Excel テキスト

日付が次のようにフォーマットされた複数の列を持つデータのバッチがあります。

2014年5月31日。

これを 05/31/2014 という便利な日付形式に変換する方法を見つけようとしています。何かアイデアはありますか?

答え1

これを実現するには、いくつかの手順が必要です。これは、数式とヘルパー セルを使用して実行する方法の 1 つです。ヘルパー セルを使用せずに実行する必要がある場合 (つまり、追加の列が必要ない場合) は、その旨をお伝えください。おそらく、このタスクを実行するための VBA を作成できると思います。

最初のタスクは、日付を年、月、日の 3 つの部分に分割することです。これら 3 つが分かれば、あとは簡単にまとめることができます。

もちろん、これはあなたの日付がいつもMMM-dd-yyyy の Rather Idiotic American Date Format© に従ってください。

これはMicrosoftのページを使用して行われました関数を使用してテキストを列に分割する例として。

  1. 月を分割する:これは、最初の月を見つける関数と、それを切り取る 分割ツール
    を使用して実現されます。search-Left

    =LEFT(A1, SEARCH("-",A1,1)-1)

    これはMayあなたの例から返されます。

  2. 日を分割する:
    似ていますが、関数のより複雑な使用MIDが必要です。 1 番目と 2 番目の位置を見つけ-、1 番目の位置から 2 番目の位置を減算して必要なテキストの長さを取得する必要があります。この結果はかなり長い関数になります。

    =MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)

    はSEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1)長さを返しますが、 はSEARCH("-",A1,1)+1単に最初の位置を取得します。MIDツールはそれらを切り取って、23例から返します。

  3. 1 年を分割する:
    最後に必要なのはツールですRIGHT。

    =RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1))

    これは単に文字列の長さを取得し、それを 2 回検索して、左側 (または右側) にあるすべてのものを返します。

問題は、月がまだ使用できないテキスト形式になっていることです。使用できるようにするには、DATEVALUEツールを使用して変換する必要があります。このツールは、月だけを受け入れることはできませんが、文字列は受け入れますMMM YY。次に、年を連結した後、月のテキストを数値に解析することができます。

  1. 月を数値として取得する:
    議論のために、最初の関数をセル B1 に格納したとします。Excelが内部で使用する日付値の文字列を返します。これを関数にラップして、月の数値を返すこと
    DATEVALUE(B1 &" 01")ができます。MONTH

    MONTH(DATEVALUE(B1 &" 01"))

  2. 次に、これらすべてを 1 つのDATE(year,month,day)関数に結合して、Excel で使用できる実際の日付に変換します。月はセル B1、日はセル C1、年は Stupid American Date Format™ に従って D1 に入力されます。

    DATE(D1, B1, C1)

    23/05/2014その後、セルに戻ります。

  3. 本当に凝った計算をしたい場合は、上記の式をすべて 1 つに組み合わせることもできます。

=DATE(RIGHT(A1,LEN(A1)-SEARCH("-",A1,SEARCH("-",A1,1)+1)),MONTH(DATEVALUE(LEFT(A1, SEARCH("-",A1,1)-1)&"  1")),MID(A1,SEARCH("-",A1,1)+1,SEARCH("-",A1,SEARCH("-",A1,1)+1)-SEARCH("-",A1,1)-1))

答え2

もう一つの簡単な方法は、次のような文字列を取ることです。2015年5月31日それを変換する2015年5月31日この変換により、日付値()式。

データがA1、 でB1入力：

=DATEVALUE(SUBSTITUTE(MID(A1,FIND("-",A1)+1,2),"-","") & "-" & LEFT(A1,3) & "-" & RIGHT(A1,4))

ここに画像の説明を入力してください

答え3

質問はすでに回答済みであり、すべての入力に感謝します。念のため、誰かの役に立つかもしれないので、この別のオプションを追加したいと思います。

=DATEVALUE(CONCATENATE(MID(A1,5,2),"-",LEFT(A1,3),"-",RIGHT(A1,4)))

これにより、日付が 2014 年 5 月 31 日形式から 2014 年 5 月 31 日形式に変換されます。私の特定のケースでは、月が 3 文字のみで入力されていることに注意してください (例: Jul)

ありがとう！

関連情報