今日を乗り切るExcel研究所

Excel に働かされていませんか

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

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

f:id:shego:20171123223825p:plain

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

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

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

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

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

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

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

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

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

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日")
1月2日 =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日")
平成29年1月2日 =TEXT(A1, "[DBNum3]ggge年m月d日")
2017/1 =TEXT(A1, "yyyy/m")
2017年1月 =TEXT(A1, "yyyy年m月")
平成29年1月 =TEXT(A1, "ggge年m月")
平成29月1日 =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+1Alt+TCtrl+C
    • 「分類」リストが「日付」になっていたら、「ユーザ定義」に切り替えてください(Alt+CEnd
  2. 別のセルに、数式で TEXT 関数を入力し、1番目の項目に日付セルの参照、2番目の項目に日付書式を入力します
    • 日付書式はダブルクオート(")で囲み、書式内の余分なダブルクオートは削除します。
  3. 日付が文字列として表示されます。
    • 必要なら、数式を他のセルにもコピーします。
  4. 数式セルを選択し表示形式を「文字列」に変更します
    • ショートカット : Ctrl+1Alt+CEnd↑↑
  5. 数式セルをコピーしてそのまま「値として貼り付け」してテキスト(文字列データ)に変換します。
    • ショートカット : Ctrl+CCtrl+Alt+VVEnter
    • 必要なら、これをさらにもとの日付セルにコピー&ペーストします

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

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

そのテキストを Excel シートに貼り付けして戻せば、文字列データとして入力できます。

【手順例】

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

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



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

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

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

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

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

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

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()
    Application.CutCopyMode = False
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
        '.Clear  ' ???
        .SetText ""
        .PutInClipboard
    End With
End Sub

【使い方】

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

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

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

まとめ

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

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

関連記事

www.shegolab.jp