エクセルで日付へ変更するには

質問

エクセル初心者です。
エクセルのリストで生年月日が全角でスペースが入り混じって入力されています。文字と認識されています。
例えば、１７. ８.　５という風に入力されています。
これらの入力済みの大量のデータを日付として例えば、17.08.05という具合に半角で日付として処理されるように、かえるにはどうしたらいいのでしょうか?

回答No.2

Wendy02 · ベストアンサー

こんにちは。

　=("H"&SUBSTITUTE(ASC(B1)," ",""))*1

で、書式－セル－ユーザー書式で、「ee.mm.dd」

マウス右クリック
コピー－形式を選択して貼り付け－値

これでは間に合わないようでしたら、

シートタブのコードの表示をクリックして、以下を貼り付けてください。
そのままのプログラムですと、隣にシリアル値（書式付）で吐き出されます。もし、上書きするのでしたら、
Const flg As Boolean = False にすると、上書きされます。

'<シートモジュール可>
Option Explicit
Sub HenkanDate()
　Dim Rng As Range, c As Range
　Dim myDate As Date, buf As String
　'ユーザー設定
　'==================================================
　Const flg As Boolean = True 'セルの隣に出す True/上書き False
　'==================================================
　Const HeY As Integer = 1988 ''(1989-1)
　Set Rng = Range(ActiveCell, ActiveCell.End(xlDown))
　Application.ScreenUpdating = False
　For Each c In Rng
　　If VarType(c) = vbString Then
　　　buf = WorksheetFunction.Substitute(StrConv(c.Value, vbNarrow), _
　　　"　", "") '半角除去
　　　buf = WorksheetFunction.Substitute(buf, ".", "/")
　　　buf = Mid(buf, 1, InStr(buf, "/") - 1) + HeY & _
　　　Mid(buf, InStr(buf, "/")) '1988を足す
　　　On Error Resume Next
　　　myDate = CDate(buf) '日付変換
　　　If Err.Number = 0 Then
　　　　With c.Offset(, Abs(CInt(flg)))
　　　　　.NumberFormatLocal = "ee.mm.dd" '書式を変える
　　　　　.Value = myDate '日付代入
　　　　End With
　　　　Err.Clear: myDate = 0
　　　End If
　　End If
　Next c
　Application.ScreenUpdating = True
End Sub

貼り付けたら、Alt + F11(Altキーを押しながらF11を押す） で、戻りますので、「日付の列のセルの一番上の所」にセルポインターをおいて、

Alt + F8で、Sheet?.HenkanDate がありますから、それをクリックしたら、日付を変換していきます。

お礼

いやーーーー!!すごいですねー。
びっくりしました。
すごいです!すごいです!
感動しました。
最初に教えていただいた関数もVBAも完璧でした!!!
私は初心者でVBAなんて全く分からないのですが
教えていただいたとおりに実行したら完璧でした!!

鳥肌がたちました。

初心者の私にすばらしい世界の一端を垣間見せていただいたような気持ちです。

あーーーーすごいなぁーーー!!
すごいよー!!
ものすごく助かりました!!

私も修行を積んでWendy02さんのようになれるように
頑張りたいです。

分からないことばかりなのでまたWendy02さんに教えていただけたら最高です。

よろしくお願いいたします

本当に本当にありがとうございました!!!!!

回答No.1

fukkyse

この文字列が「A1」に入力されているとして、隣に一列つくり、

=DATEVALUE("H"&A1)

として、セルの書式にて「ee.mm.dd」とし、必要に応じてコピー。

さらに、コピーしたものを「形式を選択して貼り付け」→「値」、必要に応じて、基入力部分を削除。

お礼

アドバイスありがとうございます。
実際に試してみたら、スペースが混じっているものは
エラーになってしまうのでだめでした。