米国からの時間データの変換について

関数で時間の変換が出来ないかと苦戦しております。
データ日付はA列に「10-Apr-2017 02:31PM」の形であり、これを日本形式の
・「2017/4/10　14：31」
もしくは
・２列にわたってB列に「2017/4/10」、C列に「14：31」と入力したいです。

今「=IF(A1="","",(MID(A1,1,2)&"/"&MID(A1,4,3)&"/"&MID(A1,8,4)&" "&MID(A1,13,7)))」
で入力したところ
「10/Apr/2017　02：31PM」で答えが返ってきます。これをコピーして別セルにテキスト貼り付けすると「2017/4/10　0：00」と近い形までになるのですが、正解にはならず詰まってます。

米国からのデータで書式の変更（時刻変更やユーザ定義による変更）は一切反応しません。

区切り位置の変更で、「10-Apr-2017」と「02:31PM」に分けてみたものの「2017/04/10」と「14：31」への変換もできずお手上げとなりました・・・

VBAを使わずに変換できるのでしょうか？

なお、エクセルは2013にて作業しております。

質問者からの補足コメント

• 

  1,2のご回答については、お礼回答欄に記載しましたとおり＃VALUE!で帰ってきてしまいます。
  
  ひとまず、「10-Apr-2017 02:31PM」をまず分解して①「10-Apr-2017」②「02:31PM」の2つのセルに分けました。②については、02：31とPMの間に半角空白を入れてTIMEVALUE関数をかけたところ「14：31」と表記され書式も時刻で認識となりました。
  ①については、このまま、2017/04/10、10/Apr/2017、2017/Apr/10など思い当たる限り変更してDATEVALUE関数をかけても＃VALUE!となってしまいます・・・
  
  取り急ぎMid,Left関数、置換を使って見せるようには2017/04/10（書式：標準テキスト）としましたが今後のために何とか日付認識にさせたいなぁと思ってるところです・・・

    補足日時：2017/04/11 11:42

No.7 ベストアンサー 回答者： WindFaller 回答日時： 2017/04/11 14:02

#2の回答者です。

少しお聞きしなければならないのは、そのデータは、ネットなどから持ってきたものですか？こちらでも、いろいろ試してみましたが、#VALUE! というエラーになるのは、特殊な条件下しか思い当たらないのです。いわゆるネットなどから持ってくる、文字コードについている識別信号(BOM)や &nbsp などです。

その場合、基本的には、Clean関数を使うのが一般的です。

日付だけ
=MID(CLEAN(A1),1,FIND(" ",CLEAN(A1))-1)*1

=MID(CLEAN(A1),1,FIND(" ",CLEAN(A1))-1)*1+MID(A1,FIND(" ",A1)+1,5)+(RIGHT(A1,2)="PM")*12/24

ただ、こうした場合を考えると、マクロによるデータの抜き出しも考えなくてはなりませんね。文字として認識している限りは、マクロで可能です。

この回答へのお礼

ちょうど今、私もそこを調べてました。ネットから持ってきたデータというか、ネットを介したデータではあります。（米国Googleが提供するデータサービスを契約しており、そのサービスURLから適宜抽出条件を入れてデータを抽出しております）

皆さんに頂いた数式が別シートに自分で手打ちしたものには有効で、コピペしたものには＃VALUE!値が返ってきてしまうので・・・＃2さんがおっしゃるとおり、Clean関数を使ったところ全て問題なくなりました。

お礼日時：2017/04/11 14:14

No.6 回答者： mike_g 回答日時： 2017/04/11 11:04

=REPLACE(A1,18,," ")*1

この回答へのお礼

すごいです！REPLACEは思いつきませんでした。関数としても長い式でなくメンテナンスもしやすくてベストですね！！ありがとうございます。

お礼日時：2017/04/11 14:06

No.5 回答者： yokomaya 回答日時： 2017/04/11 07:47

=VALUE(LEFT(A1,17))で日時が出そうなので

日時分離は
=DATEVALUE(LEFT(A1,17))
=TIMEVALUE(LEFT(A1,17))
でいけませんか？

この回答へのお礼

ありがとうございます。02：31PMを別セルに抜き出して、数字とPMの間に半角の空白を入れてTIMEVALUEをかけたところ希望通りの14：31表記となりました！
ただ、10-Apr-2017に関してはうんともすんとも・・・いろんな形式に加工したり、書式設定を変更してみたりしましたが＃VALUE!の嵐です

お礼日時：2017/04/11 11:31

No.4 回答者： tom04 回答日時： 2017/04/10 22:12

No.1・3です。

何度もごめんなさい。
投稿後気づきました。
No.3はNo.2さんの回答とダブっていました。

どうも失礼しました。m(_ _)m

この回答へのお礼

皆様ありがとうございました。締め切り押すと補足追加が出来なくなるのをしらず、こちらで失礼します。
急かされていたということもあり、目に見えないゴミ的なものがネットからのデータにはありえることを失念しており、CLEAN関数からの皆様の数式で解決いたしました。色々な形式で日付を見たいと指示を受けていますので、皆様の数式全て活用させていただきます。
＃2さんをベストアンサーにさせては頂きましたが、ご回答いただきました皆様全てベストアンサーです。本当に助かりました。ありがとうございました。

お礼日時：2017/04/11 14:39

No.3 回答者： tom04 回答日時： 2017/04/10 22:07

No.1です。

No.2さんの回答を拝見して・・・
もっと簡単に考えれば大丈夫でした。

=IF(A1="","",LEFT(A1,FIND(" ",A1)-1)*1+MID(A1,FIND(" ",A1)+1,5)*1+(RIGHT(A1,2)="PM")/2)

にしてみてください。

※　シリアル値が表示されるはずですので
セルの表示形式は適宜変更してください。m(_ _)m

この回答へのお礼

再びありがとうございます。だいぶ簡素化されて助かります。が！＃VALUE!になってしまいますね・・・

お礼日時：2017/04/11 11:28

No.2 回答者： WindFaller 回答日時： 2017/04/10 21:17

こんばんは。

=TEXT(MID(A1,1,FIND(" ",A1)-1)*1+MID(A1,FIND(" ",A1)+1,5)+(RIGHT(A1,2)="PM")*12/24,"yyyy/mm/dd HH:MM")

シリアル値だけでしたら、
=MID(A1,1,FIND(" ",A1)-1)*1+MID(A1,FIND(" ",A1)+1,5)+(RIGHT(A1,2)="PM")*12/24

一応、VBAと比較してみたけれども、VBAとやっていることは差がありませんでした。
区切り位置ツールなら、もっと簡単なはずですが……。

この回答へのお礼

ありがとうございます。かなり近づきました！！が、該当のデータを使うと＃VALUE!になってしまいますね・・・なにか米国サイトのデータに特殊な変換とかがあるのでしょうかね

お礼日時：2017/04/11 11:27

No.1 回答者： tom04 回答日時： 2017/04/10 20:12

こんばんは！

何段階かに分けた方が判りやすいかもしれませんが、
無理やり一気にやってみました。

前提条件として「日付」と「時刻」の間は半角スペースがあるとします。

表示したいセルに

=IF(A1="","",DATE(MID(A1,FIND("#",SUBSTITUTE(A1,"-","#",2))+1,4),MATCH(MID(A1,FIND("-",A1)+1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0),LEFT(A1,FIND("-",A1)-1))+MID(A1,FIND(" ",A1)+1,2)/24+(RIGHT(A1,2)="PM")*0.5+MID(A1,FIND(":",A1)+1,2)/24/60)

こんな感じではどうでしょうか？

※　個人的には長い数式はメンテナンスの面からみても好きではありません。
他の列を利用し、「年」「月」「日」「時刻」など分割して考えた方が
間違いが少ないと思います。m(_ _)m

この回答へのお礼

ありがとうございます。そうなんです、長い数式あまり入れたくないですよね・・・データが米国のサイトからダウンロードしたもので互換性とか何か違うのでしょうかね・・・頂いた式ですと＃VALUE!になってしまいます。（質問の補足にも記入してみました）

お礼日時：2017/04/11 11:17