ここから本文です

エクセル 文字列の日付を日付形式にする方法 あるデータをCSVでダウンロードし...

nir********さん

2018/6/2717:34:42

エクセル 文字列の日付を日付形式にする方法

あるデータをCSVでダウンロードし、エクセルで集計しています。

日付の列がいくつかあるのですが、「2018/05/30(水)」のような形で出力されます。
エクセルでは日付として期間などを計算したいので、日付形式にしたいです。

何列もあるので、「(水)」の曜日部分を置換で削除するのは時間がかかります。
なにか良い方法はないでしょうか。

閲覧数:
123
回答数:
3
お礼:
100枚

違反報告

ベストアンサーに選ばれた回答

Prometheusさん

2018/6/2809:41:00

「VBScript」による回答ですので、「Windows限定」です。

このプログラムは、これから処理したい「csv」ファイルを1つだけ、プログラムファイルにドラッグ&ドロップすると、「(水)」の部分を削除した「DeleteWeekname.csv」というファイルを、同じフォルダ内に作成します(元の「csv」ファイルは、一切いらっていないので、安心して試してみてください)。

なお、「曜日」の英語は、多分「Weekname」などではないと思いますが、今は、分かりやすくしているだけです。

また、エクセルの機能は全く使っていません。

「csv」ファイルから、直接「csv」ファイルを作成しています。

また、今回、データの中から、「日付(曜日)」だという判断は、先頭4文字が数字で、5文字目と8文字目が半角の「/」、6文字目7文字目が数字、9文字目10文字目が数字、11文字目が半角の「(」、最後の文字が半角の「)」というすべての条件がそろったばあい、「日付(曜日)」のデータである、と判断しています。

以下のプログラムを、メモ帳かテキストエディタに貼り付け、「~.vbs」という名前で保存します。

「~」の部分は、何でもかまいませんが、「.vbs」の部分は、必ず、半角です。

できたプログラムファイル(「~.vbs」ファイル)に、これから処理したい「csv」ファイルを1つだけ、ドラッグ&ドロップするだけです。

最後に「Finished!」と表示しますので、「OK」を押して、終了してください。

Option Explicit
Dim a, cr, cv, d1, d2, d3, d4, d5, d6, d7, f, i, m, so, wa, x, z
Set so = CreateObject("Scripting.FileSystemObject")
Set wa = WScript.Arguments
If wa.Count <> 1 or LCase(so.GetExtensionName(wa(0))) <> "csv" Then
MsgBox("ドラッグ&ドロップできるのは、csvファイル1つだけです")
WScript.Quit
End If
f = so.GetParentFolderName(wa(0))
Set cv = so.OpenTextFile(wa(0), 1)
Set cr = so.OpenTextFile(f & "\DeleteWeekname.csv", 2, True)
Do Until cv.AtEndOfStream
a = Split(cv.ReadLine, ",")
z = ""
For i = 0 to UBound(a)
x = Trim(a(i))
If Len(x) > 12 Then
d1 = Left(x ,4)
d2 = Mid(x, 5, 1)
d3 = Mid(x, 6, 2)
d4 = Mid(x, 8, 1)
d5 = Mid(x, 9, 2)
d6 = Mid(x, 11, 1)
d7 = Right(x, 1)
If IsNumeric(d1) = True and d2 = "/" and IsNumeric(d3) = True and _
d4 = "/" and IsNumeric(d5) = True and d6 = "(" and d7 = ")" Then
m = Left(x, 10)
Else
m = a(i)
End If
Else
m = a(i)
End If
z = z & m & ","
Next
cr.WriteLine Left(z, Len(z) - 1)
Loop
cv.Close
cr.Close
Set cv = Nothing
Set cr = Nothing
Set wa = Nothing
Set so = Nothing
MsgBox("Finished!")

簡単な説明です。

Option Explicit

「厳密に」というような意味ですが、気にしないでください。

Set so = CreateObject("Scripting.FileSystemObject")

ファイルやフォルダを扱えるようにしていますが、今回は特に、「csv」というテキストファイルを扱うのにも必要です。

Set wa = WScript.Arguments
If wa.Count <> 1 or LCase(so.GetExtensionName(wa(0))) <> "csv" Then
MsgBox("ドラッグ&ドロップできるのは、csvファイル1つだけです")
WScript.Quit
End If

ドラッグ&ドロップされるのを待っていて、ドラッグ&ドロップされると、その数や拡張子を調べ、想定外なら、メッセージを表示して、プログラムそのものを終了してしまいます。

f = so.GetParentFolderName(wa(0))

ドラッグ&ドロップされたファイルの存在するフォルダを調べています。

Set cv = so.OpenTextFile(wa(0), 1)

ドラッグ&ドロップされたファイルを「読み込み専用」で開いています。

Set cr = so.OpenTextFile(f & "\DeleteWeekname.csv", 2, True)

同じフォルダ内に「DeleteWeekname.csv」ファイルを、「書き込み専用」で、新規作成しています。

Do Until cv.AtEndOfStream

ファイルの終端まで処理。

a = Split(cv.ReadLine, ",")

1行読み込んで、区切り記号(今回は「,」)を使って、配列変数に格納しています。

たとえば、読み込んだ1行が「a,b,c」の場合、「a(0) = "a"」、「a(1) = "b"」、「a(2) = "c"」となります。

z = ""

書き込み用変数の初期化。

For i = 0 to UBound(a)

「UBound()」というのは、配列変数の添え字(「()」内の数字)の最大値を返します。

上記の例でしたら、「a(2) = "c"」の「2」です。

x = Trim(a(i))

前後に無用のスペースがあるとやっかいなので、削除しています。

If Len(x) > 12 Then

まず、エクセルで言う1つ1つのセルの値が12文字より長ければ、

d1 = Left(x ,4)
d2 = Mid(x, 5, 1)
d3 = Mid(x, 6, 2)
d4 = Mid(x, 8, 1)
d5 = Mid(x, 9, 2)
d6 = Mid(x, 11, 1)
d7 = Right(x, 1)

分割しています。

「d1」には「2018」、「d2」には「/」といった具合です。

If IsNumeric(d1) = True and d2 = "/" and IsNumeric(d3) = True and _
d4 = "/" and IsNumeric(d5) = True and d6 = "(" and d7 = ")" Then

「2018/06/28(~)」という条件を満たしていれば、

m = Left(x, 10)

頭の10文字だけを取り出します(すなわち「2018/06/28」の部分です)。

Else

それ以外の場合は、

m = a(i)

そのままの値を使います。

End If

「2018/06/28(~)」の形に合致していた場合の処理が終わりました。

Else

今度は、文字列の長さが「12」文字より長くなかった場合です。

m = a(i)

やなり、そのままの値を使います。

z = z & m & ","

各データを「,」でつないでいます。

Next

を、1行分繰り返しています。

cr.WriteLine Left(z, Len(z) - 1)

最後に「,」が付いてしまうので、すの「,」以外の部分を書き出しています。

Loop

を、ファイルの終端まで繰り返しています。

cv.Close
cr.Close

両ファイルを閉じています。

Set cv = Nothing
Set cr = Nothing
Set wa = Nothing
Set so = Nothing
MsgBox("Finished!")

あとは、終了処理で、最後に「Finished!」と表示しています。

  • 質問者

    nir********さん

    2018/6/2909:34:59

    回答ありがとうございます。
    手順の通りVBScriptを作り、CSVを投入して新しいファイルができあがったのですが、曜日が残ったままでした。

    条件でいれてくださったように、最初の四文字が数字、5文字目は/、6文字目7文字目は数字・・・なので、条件はあっているように思うのですが・・・なにか考えられる原因があれば教えてくださいm(__)m

  • その他の返信(4件)を表示

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

nou********さん

2018/6/2718:29:32

別の、空き列、
必要範囲、全体に、
=DATEVALUE(LEFT(日付セル,LEN(B4)-3))

又は、
=DATEVALUE(LEFT(日付セル,FIND("(",B4)-1))

と、し

値貼り付けで、書き戻しては
如何ですか?

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

all********さん

2018/6/2717:55:44

他のセルで (1文字) のデータないなら

Ctrl+H 置換え
検索する文字列:(?)
置換後の文字列:何も入力しない
すべて置換で 日付になる

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる