Excelで英語表示された日時の変換

質問

Excelで下記のようなテキストがあります。

(１) Jan 7 2013 8:22AM
(2） Jul 30 2012 11:42AM

これらを「yyyy/mm/dd HH:mm」で表示したいのですが、
どのような関数を利用したらよいでしょうか。

宜しくお願いします。

回答No.4

keithin · ベストアンサー

＞(1) や(2) は表示に含まれておりません

=SUBSTITUTE(LEFT(TRIM(A1),LEN(TRIM(A1))-2)," ",", ",2)+IF(RIGHT(TRIM(A1),2)="PM",0.5)
とかでも。

不要ならTRIMは無くても構いません。

お礼

有難うございました。完璧です！今回はお世話になりました。

回答No.3

MarcoRossiItaly

スペースによって文字列中の各要素が分かれているのですね？そうであれば、数式だけでなく、「区切り位置」の機能を併用することをお勧めします。

添付図の A 列と同一のデータが C 列にあり、C 列に対して区切り位置の処理をすることにします。C 列のデータ全体あるいは列全体を選択した状態でデータタブ「区切り位置」クリック。「カンマやタブなどの…」を指定し「次へ」。「スペース」をチェックし、「完了」。

すると、C ～ G 列のようになります。次式を入力し、最後に I 列に「yyyy/mm/dd hh:mm」を設定。

I1　=0+(e1&"-"&d1&"-"&f1&" "&substitute(substitute(g1,"AM"," AM"),"PM"," PM"))

なおこの数式は「AM」の直前にスペースを挿入していますが、それをすると、時刻の部分の文字列を Excel が時刻のシリアル値に変換してくれるのです。年月日と時刻の間にスペースを入れているのも、そうすることで日付と時刻のシリアル値の合算と認識される仕様です。

お礼

有難うございました。皆様の知識に感服いたしました。

回答No.2

tom04

こんばんは！
VBAになってしまいますが、一例です。

データはA列の1行目からあるとします。
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long
i = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
Range("B:D").Insert
Range(Cells(1, 2), Cells(i, 2)).Formula = "=SUBSTITUTE(A1,"" "",""*"",3)"
With Range(Cells(1, 3), Cells(i, 3))
.Formula = "=LEFT(B1,FIND(""*"",B1)-1)"
.Value = .Value
End With
With Range(Cells(1, 4), Cells(i, 4))
.Formula = "=MID(SUBSTITUTE(SUBSTITUTE(B1,""AM"",""""),""PM"",""""),FIND(""*"",B1)+1,LEN(B1))*1+IF(ISNUMBER(FIND(""PM"",B1)),0.5,0)"
.Value = .Value
End With
Range(Cells(1, 3), Cells(i, 3)).TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 3), TrailingMinusNumbers:=True
With Range(Cells(1, 1), Cells(i, 1))
.Formula = "=C1+D1"
.Value = .Value
.NumberFormatLocal = "yyyy/mm/dd h:mm"
End With
Range("B:D").Delete
Application.ScreenUpdating = True
End Sub 'この行まで

ちゃんと表示されればよいのですが・・・m(_ _)m

お礼

有難うございました。皆様の知識に感服いたしました。

回答No.1

keithin

（１）とかから記入されているんですね。
全角半角とかスペースの有無とか、「実際のデータと違う」ところで関数が動かない可能性が高まります。

とりあえず全部ありという事にして、その分メンドクサい数式にします。
=REPLACE(TRIM(REPLACE(LEFT(A1,LEN(A1)-2),1,FIND(")",ASC(A1)),"")),7,0," , ")+IF(RIGHT(A1,2)="PM",0.5)

（数字）とか余計なスペースの除去などの前処理を丁寧に行ってから計算させることで、数式ももっとスマート(簡単)になりますので、適切に応用してみて下さい。

補足

申し訳ありません。記載方法が悪かったのですが、(1) や(2) は表示に含まれておりません。関数での表示をしたいので、参照にさせていただきたいのですが、かっこ表記のREPLACE関数を削除しただけでは＃VALUE!となり表示されませんでした。

=REPLACE(TRIM(A1),7,0," , ")+IF(RIGHT(A1,2)="PM",0.5)

3時間ほど格闘したのですが解消せず、再度アドバイスをいただきたくお願い致します。