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

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

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

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

宜しくお願いします。

No.4 ベストアンサー 回答者： keithin 回答日時： 2013/05/27 18:22

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

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

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

この回答へのお礼

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

お礼日時：2013/05/28 19:20

No.3 回答者： MarcoRossiItaly 回答日時： 2013/05/24 01:11

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

添付図の 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 が時刻のシリアル値に変換してくれるのです。年月日と時刻の間にスペースを入れているのも、そうすることで日付と時刻のシリアル値の合算と認識される仕様です。

この回答へのお礼

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

お礼日時：2013/05/27 12:15

No.2 回答者： tom04 回答日時： 2013/05/22 23:50

こんばんは！

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

この回答へのお礼

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

お礼日時：2013/05/27 12:15

No.1 回答者： keithin 回答日時： 2013/05/22 23:28

（１）とかから記入されているんですね。

全角半角とかスペースの有無とか、「実際のデータと違う」ところで関数が動かない可能性が高まります。

とりあえず全部ありという事にして、その分メンドクサい数式にします。
=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時間ほど格闘したのですが解消せず、再度アドバイスをいただきたくお願い致します。

補足日時：2013/05/27 14:51