Excel2010（和暦に変換）

質問

以前2000バージョンでH23.09.26と入力した値を
=TEXT(A1,"GGG")&RIGHT(TEXT(A1,"_0E年"),3)&TEXT(MONTH(A1),"??月")&TEXT(DAY(A1),"??日"）で平成23年 9月26日と表示させていました。
しかし、2010バージョンでは、#VALUE!となってしまいます。
H23.09.26は文字列で入力してあります。
ためしに、H23.09.26を2011.09.26と入力し直したところ、平成23年 9月26日と表示されます。
H23.09.26を今まで通り平成23年 9月26日と表示させるにはどうしたらいいのでしょうか？
（TEXTの関数を用いた理由は、日付の桁数を合わすためです。）
　(例)
　　平成　９年　３月　５日
　　平成２０年１２月　７日
　　平成２３年１０月１０日

回答No.5

kagakusuki · ベストアンサー

>2000バージョンの場合「H23.9.26」と入力すると、数式バーの表示は「2011.09.26」となり、セルの表示形式は「日付」になるのに対し、2010バージョンでは、「H23.9.26」と入力すると、数式バーの表示は同じ「H23.9.26」でセルの表示形式は「標準」でした。ここが問題のような気がします。

　それは、セルの書式設定の表示形式が[文字列]となっているためです。
　只、少なくとも私が使用しているＥｘｃｅｌ2010では、書式設定が[文字列]となっている場合であっても、質問者様の関数で、正常に変換する事が出来ております。

　私にも原因が判らないため、解決策とはならない可能性も少なくはありませんが、試してみる価値がありそうな事を、幾つか御伝え致します。

>データを確認したところ何も余計なものは入っていませんでした。

●もしかしますと、無駄足になるかも知れませんが、空白や改行以外にも、表示には表れない文字は幾つもありますので、念のために、適当な空きセルに

=LEN(A1)

という関数を入力して、関数を使って求めた文字数と、表示を見て数えた場合の文字数に、違いが無いかを確認してみて下さい。
　もしも、関数を使って求めた文字数の方が多ければ、どこかに見えない文字が隠れているという事になりますので、次の段階として、適当な使われていない行の中のセルに

=IF(COLUMNS($A:A)>LEN($A1),"","□"&MID($A1,COLUMNS($A:A),1)&"□")

という関数を入力してから、そのセルをコピーして、右方向にあるセル範囲に貼り付けてみて下さい。
　すると、A1セルに入力されている文字列の中の文字が、1文字ずつ順番に□で挟まれた形で現れますから、□が2個表示されているだけで、間に文字が無い様に見えるセルが現れないかどうかを確認して下さい。
　もしも、表示されない文字が存在している事が判り、その文字が何という文字なのかが判明すれば、Ｅｘｃｅｌの置換機能を利用して、その文字を一括して削除する事が出来る可能性があります。

●それから、参照先の文字列中の単語間のスペースを 1 つずつ残して、不要なスペースを全て削除した文字列を出力する機能を持つTRIM 関数という関数があります。
　又、参照先の文字列中に存在する印刷出来ない文字を削除した文字列を出力する機能を持つCLEAN関数という関数があります。
　これらを使えば、見えない余計な文字を取り除く事が出来る可能性がります。
　但し、Ｅｘｃｅｌに標準で登録されている、数多くの文字の中の見えない文字の中には、どちらの関数も、単独で使用したのでは取り除く事が出来ない文字がありますし、両方の関数を使用しても取り除けない文字も、少なくとも１つは存在します。

●おそらく駄目だとは思いますが、

=TEXT(A1,"GGG")&RIGHT(TEXT(A1,"_0E年"),3)&TEXT(MONTH(A1),"??月")&TEXT(DAY(A1),"??日"）

と似た様な働きをする関数として、

=IF(ISNUMBER(DAY(A1*1)+1/A1*1),TEXT(A1*1,"ggg")&TEXT(A1*1,IF(TEXT(A1*1,"E")*1<10," ","")&"E年")&TEXT(A1*1,IF(MONTH(A1*1)<10," ","")&"m月")&TEXT(A1*1,IF(DAY(A1*1)<10," ","")&"d日"),"")

という関数を、ANo.3を投稿する前に思いついておりましたが、質問者様の関数をこちらで試した際に問題が無かったため、前回の回答では提示致しませんでした。
　万が一、こちらの関数でならば動作するという事もあるかも知れませんから、試しに適当な空いている列に、この関数を入力してみて下さい。

●もし、元となる日付データが関数によって定められているのではなく、値が直接入力されたものである場合には、セルの書式設定の表示形式を[標準]とした上で、元となる日付データが入力されているセル範囲をコピーして、同一のセル範囲に[値のみ貼付け]てみては如何でしょうか？

>「H23.9.26」でセルの表示形式は「標準」のものを、「日付」で「2011.09.26」に変換する方法はありませんか？

●通常であれば、例え文字列として「H23.9.26」と入力されていても、質問者様の関数で、正常に変換する事が出来る筈なのですが、それが出来ないという事は、次に述べる方法でも変換出来ない可能性がありますので、そのつもりで御読み下さい。
　数値に変換する事が可能な文字列であれば、

=VALUE(A1)

=A1*1

=A1+0

の何れの関数でも、数値データに変換する事が出来ますから、使用していない適当な列に、上記の関数を入力して、数値データに変換したデータを得てから、そのセル範囲をコピーして、元の日付データが入力されているセル範囲に、[値のみコピー]されると良いと思います。

●最悪、全ての方法が駄目だった場合には、少なくとも入力し直せば正常に動作する事は判っているのですから、日付が入力されている全てのセルを、しらみつぶしにダブルクリックして行けば、同一のデータを入力し直した事になります。（関数によって値が定まっているセルに対しては有効でありません）

お礼

色々と教えて頂きありがとうございました。
とても参考になりました。

(1)　=TEXT(A1,"GGG")&RIGHT(TEXT(A1,"_0E年"),3)&TEXT(MONTH(A1),"??月")&TEXT(DAY(A1),"??日"）

(2)　=IF(ISNUMBER(DAY(A1*1)+1/A1*1),TEXT(A1*1,"ggg")&TEXT(A1*1,IF(TEXT(A1*1,"E")*1<10," ","")&"E年")&TEXT(A1*1,IF(MONTH(A1*1)<10," ","")&"m月")&TEXT(A1*1,IF(DAY(A1*1)<10," ","")&"d日"),"")

上記、(1)(2)の式は家のＰＣでは正常に表示されました。会社のＰＣで(2)はまだ試していないですが、何かExcelの設定が違うのかも知れません。
原因を調べるのと教えて頂いた数式を試してみます。
ありがとうございました。

回答No.4

kagakusuki

回答番号ANo.3です。
　私が現在使用しているExcelのバージョンもExcel2010なので、御質問文中にある

=TEXT(A1,"GGG")&RIGHT(TEXT(A1,"_0E年"),3)&TEXT(MONTH(A1),"??月")&TEXT(DAY(A1),"??日"）

の関数を試してみた処、

H9.3.5

と入力した場合には、正常に

平成 9年 3月 5日

と表示されました。
　ですから、Excel2010であるから駄目という訳ではない様です。

>H23.09.26は文字列で入力してあります。
>ためしに、H23.09.26を2011.09.26と入力し直したところ、平成23年 9月26日と表示されます。

　という事は、やはりExcelのバージョンや関数に問題があるのではなく、元データがおかしいのではないかと思います。
　入力されている日付データに、「'」や「.」等の余計なものが付いてはいないでしょうか？
　或いは

H23.09.26

と入力されているべき処を、

H23.0926

の様に、必要な「.」が抜けていたり、

H23.O9.26

の様に、数字のゼロがアルファベットのオーになっていたりはしないでしょうか？
　或いは

="'H23.09.26"

の様に、必要のない「'」が付いた関数が入力されてはいないでしょうか？
　もし、余計な「'」や「.」等が付いている場合には、日付が入力されているセル範囲をまとめて選択してから、

[ホーム]タブの「編集グループ」の[検索と選択]をクリック
　　↓
現れた選択肢の中にある[置換]をクリック
　　↓
現れた「検索と置換」ダイアログボックスの「検索する文字列」欄に

'

或いは

.

等の余計な文字を1文字だけ入力
　　↓
「置換後の文字列」欄が空欄となっている状態で、「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック

という操作で、余計な文字を一括して削除されると良いと思います。

回答No.3

kagakusuki

桁数を揃えるだけでしたら、セルの書式設定の表示形式を[ユーザー定義]の

gggee"年"mm"月"dd"日"

とすれば、例えば

平成9年3月5日
H9.3.5
1997/3/5

等と入力しただけで、

平成09年03月05日

と表示される様になるのですが、それでは駄目なのでしょうか？

補足

補足させて頂きます。

>元データがおかしいのではないかと思います。

データを確認したところ何も余計なものは入っていませんでした。
（2000バージョンの時は普通に使用できていました。）

>桁数を揃えるだけでしたら、セルの書式設定の表示形式を[ユーザー定義]の
gggee"年"mm"月"dd"日"とすれば、例えば平成9年3月5日，H9.3.5，1997/3/5等と入力しただけで、
平成09年03月05日と表示される

については、ゼロ詰めになってしまうのでダメです。ゼロ詰めではなく、空白詰めでお願いします。

関数を試して頂きありがとうございます。

正常に表示できたということで、再度、Excel2010の新しいブックを開き、最初から数式を入力し試したところ、やはりダメでした。

以前の2000バージョンと何が違うのか調べてみたところ、2000バージョンの場合「H23.9.26」と入力すると、数式バーの表示は「2011.09.26」となり、セルの表示形式は「日付」になるのに対し、
2010バージョンでは、「H23.9.26」と入力すると、数式バーの表示は同じ「H23.9.26」でセルの表示形式は「標準」でした。ここが問題のような気がします。

質問です。

　「H23.9.26」でセルの表示形式は「標準」のものを、「日付」で「2011.09.26」に変換する方法はありませんか？
（セルの表示形式を「日付」に変更し、入力し直せば正常に表示されるのですが、大量にデータがありますので、なんとかならないでしょうか）
「区切り位置」で「日付」にチェックを入れ試しましたが、表示は「H23.9.27」のままでした。
（セルの表示形式を「日付」に変更しましたが、変わりませんでした）

すみませんが、よろしくお願いします。

回答No.2

KURUMITO

回答No１です。
A１セルからA１０セルに元のデータがあるとしてそれらの範囲を選択したのちに「データ」タブの「区切り位置」を選択します。
表示の画面でそのまま「次へ」、次の画面でもそのまま「次へ」、最後の画面でデータ形式で「日付」にチェックをしてから右の窓では「YMD]を選択して「完了」をクリックします。
これでお望みの形になりますが桁数が合わないことがありますね。
なお、回答１ですが桁数を合わせることを考えればB１セルには次の式を入力してはどうでしょう。

=IF(A1="","",TEXT(VALUE(A1),"gee.mm.dd"))

回答No.1

KURUMITO

A列に元のデータがあるとしてB１セルには次の式を入力して下方にオートフィルドラッグしてはどうでしょう。

=IF(A1="","",TEXT(VALUE(A1),"ge.mm.dd"))