Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2016)
英語で短縮表記の”月”を関数で数字に認識したい
投稿日時: 20/12/13 16:57:22
投稿者: せりむ

いつもお世話になっております、本日ご相談させて頂きたいのは、
VBAを利用せずに
"Jan,Feb,Mar...,Nov,Dec"の表記を
"1,2,3,...,11,12"の数字に変換する方法についてになります。
数字から英語へ変換であれば
=text(○○,"yyyymmmdd")や[$409]などで解消できるようなのですが、
英語から数字へ変換する方法が既出の質問から見つけられず、投稿をさせて頂きました。
 
ご助力の程どうかよろしくお願い致します。
 
 
【やりたいこと】
元データが海外の銀行サイトからの出力になるのですが、
A列に入力されている日付データが、"13 Dec 20"というような形式となっており、
(数字で日付+半角スペース+短縮英語表記の月+半角スペース+年下2桁)
これらを関数にて数字表記にしたく考えております。
 
別のシステムとの兼ね合いから言語設定の変更以外の解決策を探しているのですが、
何か方法をご教授頂けないでしょうか。
 
【試した事】
=datevalue(LEFT(A1,2)&"-"&MID(A1,4,LEN(A1)-6)&"-"&"20"&RIGHT(A1,2))
にて
一度"13-Dec-2020"に変換してそれを日付データに出来れば
あとはtext関数でどうとでもなるかと思ったのですが#VALUE!が解消できませんでした。
"-"を"/"に変え並びを"2020/Dec/13"に変えても同様でした。
 
 

回答
投稿日時: 20/12/13 17:14:35
投稿者: 半平太

>text関数でどうとでもなるかと思ったのですが
私もそう思います。
 
>#VALUE!が解消できませんでした。
ここが分かりません。そんな事にならないのですけども。
 
13 Dec 20 ←これをどう変換したいのですか?
 
具体的に示してください。
(1)シリアル値(それを書式で調整)? 
(2)文字列(どんな)?
(3)それとも?

投稿日時: 20/12/13 17:25:45
投稿者: せりむ

>半平太 様
言葉が足りておらず申し訳ありません、
欲しい最終形としては数値8桁の形が慣れておりありがたく思います。
今回記載の形ですと
変換元
"13-Dec-2020"
変換後
"20201213"
になります。
よろしくお願い申し上げます。

回答
投稿日時: 20/12/13 17:34:23
投稿者: 半平太

なら、これでいいハズですが?
 
=TEXT(LEFT(A1,2)&"-"&MID(A1,4,LEN(A1)-6)&"-"&"20"&RIGHT(A1,2),"yyyymmdd")

回答
投稿日時: 20/12/13 17:42:35
投稿者: コナミ

そのままシリアル値になっているように思うのですが
文字列になっているということなんですかね?
半角スペースを半角スペースに置換とかでシリアル値にはならないのですか?
 
#VALUE!になるのは数値データだからなんだと思うんですが。

回答
投稿日時: 20/12/13 18:10:21
投稿者: WinArrow
投稿者のウェブサイトに移動

>"13-Dec-2020"
と表示されているセルの
「表示形式」と数式バーを確認してみてください。
 
数式バーが
2020/12/13

表示形式が
dd mmmm yy
だったら、当該セルは、シリアル値で入力されています。
 
表示形式を yyyymmdd に変更するだけで、解決すると思います。
 
他のセルにとりだすのでしたら
=TEXT(当該セル,"yyyymmdd")
ですね・・・

回答
投稿日時: 20/12/13 18:16:36
投稿者: WinArrow
投稿者のウェブサイトに移動

セルに
13 Dec 20
と入力すると日付(シリアr値)になります。→数式バーで確認してみてください。
 
 
仮に
文字列として
13 Dec 20
が入力されているならば、
別セルで
=TEXT(A1*1,"yyyymmdd")
としてみてください。

投稿日時: 20/12/13 18:23:12
投稿者: せりむ

>半平太 様
ありがとうございます、
頂いた関数にて同僚のPCでは動作を確認いたしまして、
(私のものでは動きませんでしたので)設定等の差異を確認いたしました所、
おそらく初めに書きました別システムの導入時にExcelの
設定>言語>Officeの編集言語と校正機能 が
英語(米国)<優先>となっておりました。
 
お手数をお掛け致しまして申し訳ございませんが、
こちらの設定上でも対応可能な何か対応策をご存じでしたら
ご教授頂けますと幸いでございます。
 
よろしくお願い申し上げます。
 
>コナミ 様
置換にてスペースの書き直しやハイフンへの置換を試みましたが
特に変化は得られませんでした。

投稿日時: 20/12/13 18:32:26
投稿者: せりむ

>WinArrow 様
自身の状況理解が足りておらず申し訳ありません、
先ほど他の方より頂きました指摘にて、どうもExcelの言語設定の影響で、
これらを日付を表すものとして認識することが出来ていないようだとわかりました。
頂きました記載直し/×1の結果はいずれもこの表記をシリアル値として認識せず
ただの文字列として認識しているように思われます。

回答
投稿日時: 20/12/14 00:10:51
投稿者: 半平太

>設定>言語>Officeの編集言語と校正機能 が
>英語(米国)<優先>となっておりました
  
その設定にすると、#VALUE!の出る現象が起こりました。
これでやって見てください。
 ↓
=20&RIGHT(A1,2)&TEXT((SEARCH(MID(A1,4,3),"JanFebMarAprMayJunJulAugSepOctNovDec")-1)/3+1,"00")&RIGHT(A1,2)

投稿日時: 20/12/14 17:02:48
投稿者: せりむ

>半平太 様
ありがとうございます、SEARCH /3…目から鱗でした。
頂戴しました関数にて無事解決いたしました。
ありがとうございました。