エクセルで数列を和暦+月日に変換

いつもお世話になっております。
excel2010にて、次のような数列を和暦+月日に変換したいと考えています。

例）3150927

3　→　1＝明治、2＝大正、3＝昭和、4＝平成
15　→　15年
09　→　9月
27　→　27日

→昭和15年9月27日

年号がクセモノで、どう処理すればいいか見当が付きません。
何卒よろしくお願いいたします。

No.1 ベストアンサー 回答者： akiomyau 回答日時： 2013/11/18 15:17

あらかじめSheet2のA1からB4までに

1明治
2大正
3昭和
4平成
という表を作りこの範囲に元号という名前を定義しておきます。
Sheet1のA1に3150927が入っているとします。
こんな式でどうでしょうか。
=VLOOKUP(VALUE(LEFT(A1,1)),元号,2)&MID(A1,2,2)&"年"&MID(A1,4,2)&"月"&RIGHT(A1,2)&"日"

この回答へのお礼

すごい、あっという間にこんなに回答が集まるなんて！
一番早くに回答をくださったakiomyauさんをベストアンサーとさせて頂きました。
バッチリ出来ました。

LEFT、MID、RIGHT関数はこれまで使ったことがありませんでしたが
文字列を操作する関数なのでしょうか。勉強になりました。
どうもありがとうございました。

お礼日時：2013/11/18 15:41

No.5 回答者： chonami 回答日時： 2013/11/18 15:29

他の方と若干かぶりますが、

=DATEVALUE(CHOOSE(LEFT(A1,1),"明治","大正","昭和","平成")&MID(A1,2,2)&"年"&MID(A1,4,2)&"月"&RIGHT(A1,2)&"日")

とかでも。
表示形式を「ggge"年"m"月"n"日"」で。

この回答へのお礼

chonamiさん、どうもありがとうございました。
いやはや、パッと出てくるなんて皆さんすごいです。

お礼日時：2013/11/18 15:44

No.4 回答者： aokii 回答日時： 2013/11/18 15:23

3150927がA1セルに有る場合で、セルの表示形式を日付の和暦にする場合は、

=VALUE(CHOOSE(LEFT($A$1,1),"明治","大正","昭和","平成")&VALUE(MID($A$1,2,2))&"年"&VALUE(MID($A$1,4,2))&"月"&VALUE(MID($A$1,6,2))&"日")

No.3 回答者： aokii 回答日時： 2013/11/18 15:20

3150927がA1セルに有る場合ですが、

=CHOOSE(LEFT($A$1,1),"明治","大正","昭和","平成")&VALUE(MID($A$1,2,2))&"年"&VALUE(MID($A$1,4,2))&"月"&VALUE(MID($A$1,6,2))&"日"

この回答へのお礼

aokiiさん、どうもありがとうございました。
これまでCHOOSEとVALUEも使ったことがなかったので、改めて調べてみたいと思います。

お礼日時：2013/11/18 15:43

No.2 回答者： shintaro-2 回答日時： 2013/11/18 15:19

>年号がクセモノで、どう処理すればいいか見当が付きません。

if文で処理すると長くなるので、数字と年号との対応を記述した別表を作成してvlookupで参照するのが簡単でしょう。

例えば
a1に数列があり、別表がD1：E５にあったとして
=(VLOOKUP(LEFT(A1,1)*1,$D1:$E5,2)&MID(A1,2,2)&"/"&MID(A1,4,2)&"/"&RIGHT(A1,2))*1
とすればOKです。

最後に表示形式で和暦表示にしてください。

この回答へのお礼

shintaro-2さん、どうもありがとうございました。

お礼日時：2013/11/18 15:42