【Excel関数活用術】文字列や数字を日付に変換する

Office365
スポンサーリンク
マネ妻
マネ妻

Excel関数を使い倒そう!
よくある実例を元に関数の使い方を紹介します。

「取引先からもらったデータや、データベースからダウンロードしたデータが、日付も含めて全部文字列になっている!」「日付に見えるけど、ただの数字だった!」っていう経験ありませんか?

日付データになっていないと、日付計算ができなかったり、フィルターで抽出できなかったり、なにかと不便です。

今回は、文字列形式や数字になっている日付を、日付データに変換する方法を紹介します。

【DATE関数】を使って日付データに変換する

下の図のように、最初の4桁が「年」、次の2桁が「月」、残りの2桁が「日」、と規則性のある8桁のデータをDATE関数を使用して日付形式のデータに変換してみましょう。

DATE関数は、3つの数値を日付のシリアル値に変換する関数です。

 Step1:左から4文字取り出して、年を出す
 Step2:左から5文字目を先頭に2文字取り出して、月を出す
 Step3:右から2文字取り出して、日を出す

数式にすると、次のようになります。

=DATE(LEFT(B2,4),MID(B2,5,2),RIGHT(B2,2))

数字を日付形式にしたい時もDATE関数で変換することができます。

【DATE関数】
=DATE(年,月,日)
[年][月][日]の数値から日付データ(シリアル値)を作成します。

【LEFT関数】
=LEFT(文字列[,文字数])
[文字列]の先頭から[文字数]分を取り出します。[文字数]を省略すると、最初の1文字を取り出します。文字数は半角/全角を区別せず、1文字と数えます。

【MID関数】
=MID(文字列,開始位置,文字数)
[文字列]を[開始位置]から[文字数]分を取り出します。[文字数]が[文字列]より長い場合は、文字列の末尾まで取り出します。文字数は半角/全角を区別せず、1文字と数えます。

【RIGHT関数】
=RIGHT(文字列[,文字数])
[文字列]の末尾(右端)から[文字数]分を取り出します。[文字数]を省略すると、1文字だけ取り出します。

【REPLACE関数】を使って数字の途中に「/」を挿入して日付データに変換する

今度は、最初の4桁が「年」、次の2桁が「月」、残りの2桁が「日」、と規則性のある8桁の数字の途中に、REPLACE関数を使って、間に「/」を挿入し、日付データに変換してみましょう。

REPLACE関数は、文字を置換するだけでなく、文字を削除したり、文字を挿入したりすることができる便利な関数です。

 Step1:左から5文字目の文字に「/」を挿入する
  →「2020/0216」になる
 Step2:Step1の左から8文字目の文字に「/」を挿入する

数式にすると、次のようになります。

=REPLACE(REPLACE(B2,5,0,”/”),8,0,”/”)

REPLACE関数を使って出した値は文字列になる

REPLACE関数の戻り値は文字列ですので、変換したC列の日付データは文字列です。
(文字列は左側に文字が寄ります)

「/」が入った日付形式の文字列なら、日付データとして扱えるので、日付計算をすることができますが、オートフィルタ機能を使うことはできません。

オートフィルタ機能を使うには、文字列を数値(シリアル値)に変換する必要があります。
文字列を数値にするには、1を掛けると簡単に変換することができます。

=REPLACE(REPLACE(B2,5,0,”/”),8,0,”/”)*1

【REPLACE関数】
=REPLACE(文字列,開始位置,0,置換文字列)
第3引数の[文字数]に「0」を指定した場合は、[文字列]の先頭から数えた[開始位置]に[置換文字]を挿入します。

マネ夫
マネ夫

DATE関数、REPLACE関数どちらも日付データに変換できますが、戻り値が違うということを覚えておくと便利です。
 DATE関数の戻り値は、数値(シリアル値)。
 REPLACE関数の戻り値は、文字列。オートフィルタが使えない。

コメント

タイトルとURLをコピーしました