シーゴの Excel 研究室

タイトル変更しました （旧称：今日を乗り切るExcel研究所）

トップ > データ > Excel セルの日付を文字列に変換したい

Excel セルの日付を文字列に変換したい

データ 書式

日付をそのままの文字列に変換する手順を検討し、それを自動化するそのマクロを作成します。

f:id:shego:20171123223825p:plain

日付が数値になってしまう

Excel が扱う日付データの実体は、単なる数値です。

セルに日付っぽいテキストを入力すると、日付データとして扱ってくれますが、このときセルの値としては勝手に数値（シリアル値）に変換されます。

それが日付に見えているのはセルの書式設定でやっていることです。 セルの表示形式に日付の書式が設定されていると、数値を基準日からの経過日数とみなして年月日や時分秒に換算し、日付形式で表示されるようになっています。

何かしてそれが外れたときに、元の数値が裸で現れることになります。

日付を見た目そのままの日付テキストとして扱いたい時には、それをなんとかして文字列データに変換しなければなりません。

日付を文字列に変換するには

これが意外に難しくて、日付セルの表示形式を「文字列」に変更してみても、数字の文字列になるだけです。

ここでは、日付を文字列に変換する方法として、「数式で TEXT 関数を使う」やり方と、「テキストエディタにいったんコピペする」やり方の２つを検討します。

TEXT 関数でテキストに変換してみる

TEXT 関数を使うとシリアル値を日付の文字列に変換できます。それには数式で TEXT 関数に日付セルへの参照と「日付書式」を指定します。

日付書式というのは日付の表示形式を決めている式で、「yyyy年m月d日」のような決められたルールに従って記述します。

残念ながら既存のセルからその表示形式（日付書式）を取得するような関数はないので、書式ルールを手で入力してやる必要があります。書式ルールを理解して入力してもいいのですが、「セルの書式設定」ダイアログの「表示形式」で基本的な書式が取得できるので、そこからコピペしてくる方が早いでしょう。

f:id:shego:20171123223855p:plain

「ユーザ定義」の「種類」テキストボックスに表示されている暗号のような文字列をそのままコピーします。 基本的に、これをそのまま貼り付けしてTEXT関数に渡せばいいのですが、日本の日付書式には余計なダブルクォーテーション（”）が挿入されているので、これらを削除しておく必要があります。

yyyy"年"m"月"d"日" ⇒ yyyy年m月d日 ⇒ =TEXT(A1, "yyyy年m月d日")

f:id:shego:20171123223947p:plain

そうなってくるともう面倒なので、よく使いそうな日付書式をそのまま数式としてコピペできるようにまとめておきます。

日付サンプル 日付書式つき数式
1月2日 =TEXT(A1, "m月d日")
１月２日 =TEXT(A1, "[DBNum3]m月d日")
H29.1.2 =TEXT(A1, "ge.m.d")
20170102 =TEXT(A1, "yyyymmdd")
2017/1/2 =TEXT(A1, "yyyy/m/d")
2017/01/02 =TEXT(A1, "yyyy/mm/dd")
2017-01-02 =TEXT(A1, "yyyy-mm-dd")
2017年1月2日 =TEXT(A1, "yyyy年m月d日")
平成29年1月2日 =TEXT(A1, "ggge年m月d日")
平成２９年１月２日 =TEXT(A1, "[DBNum3]ggge年m月d日")
2017/1 =TEXT(A1, "yyyy/m")
2017年1月 =TEXT(A1, "yyyy年m月")
平成29年1月 =TEXT(A1, "ggge年m月")
平成２９年１月 =TEXT(A1, "[DBNum3]ggge年m月")
20170102012359 =TEXT(A1, "yyyymmddhhmmss")
2017/01/02 00:00:00 =TEXT(A1, "yyyy/mm/dd hh:mm:ss")
2017-01-02 01:23:45 =TEXT(A1, "yyyy-mm-dd hh:mm:ss")
2017-01-02 01:23:45.678 =TEXT(A1, "yyyy-mm-dd hh:mm:ss.000")
2017-01-02T01:23:45+09:00 =TEXT(A1, "yyyy-mm-ddThh:mm:ss""+09:00""")

【手順例】

  1. 日付の入ったセルを選択し、「セルの書式設定」の「表示形式」から日付書式をコピーします
    • ショートカット : Ctrl+1 ⇒ Alt+T ⇒ Ctrl+C
    • 「分類」リストが「日付」になっていたら、「ユーザ定義」に切り替えてください（Alt+C ⇒ End）
  2. 日付データとは別のセルに、数式で TEXT 関数を入力し、１番目の項目に日付セルの参照、２番目の項目に日付書式を入力します
    • 日付書式はダブルクオート（"）で囲み、書式内の余分なダブルクオートは削除します。
    • 日付セルの参照は、そのセルをクリックすることで入力できます。
  3. 日付が文字列として表示されます。
    • 必要なら、数式を他のセルにもコピーします。
  4. 数式セルを選択し表示形式を「文字列」に変更します
    • ショートカット : Ctrl+1 ⇒ Alt+C ⇒ End ⇒ ↑↑
  5. 数式セルをコピーしてそのまま「値として貼り付け」してテキスト（文字列データ）に変換します。
    • ショートカット : Ctrl+C ⇒ Ctrl+Alt+V ⇒ V ⇒ Enter
    • 必要なら、これをさらに元の日付セルにコピー＆ペーストします（表示形式も「文字列」にしておきます）

　

テキストエディタを介してコピー＆ペーストしてみる

日付セルの表示形式を調べると「標準」ではなく「日付」（あるいは「ユーザ定義」）になっています。

Excel が勝手に日付テキストを「日付」形式（シリアル値）に変換するのは、入力セルの表示形式が「標準」となっているときの場合のみのようです。

あらかじめ、入力先の表示形式を「文字列」に設定しておけば、日付セルになることはありません。

コピーしたセル範囲をクリップボード経由でテキストエディタなどに貼り付けると、日付などの表示文字列がそのまま取得できます。

その後、日付セルの表示形式を「文字列」に変更したうえで、その日付テキストを Excel シートに貼り付け戻せば、テキストデータとして日付が入力でるはずです。

【手順例】

  1. 日付を文字列に変換したいセル範囲をコピーします
  2. テキストエディタ（メモ帳など）を開き、コピーしたセルデータを貼り付けます
    • セルデータがTAB区切りのテキストとして展開されます
  3. 今度はテキストエディタで、そのテキストを全選択し、コピーしておきます。（Ctrl + A ⇒ Ctrl + C）
  4. Excel に戻り、「セルの書式設定」ダイアログで、元のセル範囲の表示形式を「文字列」に変更します
    • 元の日付が数字になってしまいますが、心配ありません
  5. 元のセル範囲にテキストデータを「貼り付け」（Ctrl+V）して上書きします
  6. 日付が文字列としてセルに入力されています

シートを丸ごと文字列化していいならならこの方法が一番お手軽ですが、こまめに何度も繰り返すとなるとそれも苦痛になります。

　

セルの値を文字列データに変換するマクロ

上記のクリップボードを使ったテキスト変換を自動化するマクロ作成してみました。

本マクロは、セルの表示テキストをそのまま文字列データに変換し、セルの表示形式も「文字列」に変更するものです。

このマクロ VBA を実行すると、選択セル範囲の日付や時刻、通貨、など数値系データが表示通りの文字列データになります。

マクロ実行による文字列への変換は Undo することはできませんが、セルの書式設定を「標準」にすることで日付データに戻せるはずです。

元の日付セルの内容が数式だった場合、本マクロで数式は削除され、固定の文字列で上書きされることになるので注意してください。

Option Explicit

Sub セルの値を表示文字列に変換する()
    If TypeName(Selection) <> "Range" Then Beep: Exit Sub
    
    Dim selRng As Range
    Set selRng = Selection
    
    Dim txtRng As Range
    Set txtRng = Intersect(selRng, ActiveSheet.UsedRange)
    If txtRng Is Nothing Then Beep: Exit Sub
    
    Application.ScreenUpdating = False
    
    textize txtRng
    selRng.Select
    selRng.NumberFormatLocal = "@"
    
    Application.ScreenUpdating = True
End Sub

Private Sub textize(rng As Range)
    rng.Worksheet.EnableCalculation = False
    Dim col As Range
    For Each col In rng.Columns
        copyRangeAsText col
        col.NumberFormatLocal = "@"
        col.Select
        ActiveSheet.PasteSpecial Format:="Unicode テキスト"
    Next
    clearClipboard
    rng.Worksheet.EnableCalculation = True
End Sub

Private Sub copyRangeAsText(rng As Range)
    Dim txt As String
    rng.Copy
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .GetFromClipboard
        txt = .getText
    End With
    Application.CutCopyMode = False
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        .SetText txt
        .PutInClipboard
    End With
End Sub

Private Sub clearClipboard()
    Range("A1").Copy
    Application.CutCopyMode = False
End Sub

【使い方】

  1. 上記VBAプログラムを Excel の標準モジュールにコピー＆ペーストします
  2. 文字列データに変換したいセル範囲を選択します
  3. 本マクロを実行します
  4. 日付や数値が文字列に変換されています

【注意】 本マクロの動作結果は元に戻す（Unde）ことができません。

【注意】 セルの内容が数式だった場合、本マクロで数式は失われ元に戻せません。

まとめ

この記事では、日付の表示の仕組みと、それをなんとか文字列データに変換する手順を検討しました。

本記事の内容は Windows 10 と Excel 2013 の環境で動作確認しました。

関連記事

www.shegolab.jp

www.shegolab.jp