エクセルで生年月日の数字のみを別々の列に取り出すには…？

Question

うまく説明できるか心配なのですが…どなたか教えてください。
　A列に生年月日が以下のように入っています。
　平成9年3月15日　がセルA1
　平成13年12月4日　がセルA2
　平成2年1月1日　がセルA3
　…のようになっています。
　これをB列に年の数字のみ　9，13，2，…
　　　　C列に月の数字のみ　3，12，1，…
　　　　D列に日の数字のみ　15，4，1，…
　と取り出したい?(分けたい?)のです。
というのも、アクセスのテーブルがこの形で入力するようになっていて、エクセルのワークシートにはすべてがつながって(スペースもなく)入力されているため、上記のようにできればアクセスに貼り付けやすいと考えました。どちらのソフトも詳しくはないので、アクセスのデータベースは変更したくないのです。
どなたか知恵をお貸しください。お願いします。

maruru01 · Accepted Answer

>A1が平成9年7月11日で　B1に数式を入力すると 
>なんと明治33年1月9日と結果が出てしまいました。
B1のセルの表示形式が、自動的に日付になってしまったからです。
「--」を取ると文字列が返されるので、そのまま表示されますが、「--」を付けると数値が返されます。
（No.5の説明の通り）
すると、数式中に日付が入ったA1があるので、Excelが余計な気をきかせて、勝手に表示形式を日付にしてしまうことがあります。
Excelでは、日付というのはシリアル値という数値で管理されています。
シリアル値というのは、「1900/1/1」を「1」として、1日経過するごとに+1される値です。
（時間の経過は小数で表されます。つまり0.5で12時間）
セルの表示形式が日付のために、「明治33年1月9日」となっているセルには、
実際には、「9」という数値が入っているということです。
つまり、「明治33年1月9日」（1900/1/9）のシリアル値が、「9」ということです。
さらに言えば、「明治33年1月9日」というのは、あくまで見た目の文字で、セルの内容はまったく違う（この場合の内容は「9」）ということです。
ちなみに、A1「平成9年3月15日」の表示形式を「標準」にすると、
「35504」
という数値になります。
つまり、このA1も実際のセルの内容は、「35504」だということです。

説明が長くなりましたが、結論としては、セルの表示形式を「標準」にすればいいということです。

yomo3 · Answer

maruru01さん。ありがとうございます。ここでお礼を言うのも変ですが、私も参考になりました。
で、
>A1が平成9年7月11日で　B1に数式を入力すると 
>なんと明治33年1月9日と結果が出てしまいました。 
の件ですが、
maruru01さんのおっしゃることが原因ではあるのですが、書式が日付表示になってしまった原因は、A列のとなりのB列で列を挿入したからではないかと思います。
列を挿入すると、挿入列の左隣の書式が引き継がれます。
A列の書式が和暦の表示形式になっているので、このようなことが起きたのだと思います。
いずれにしても、表示形式を「標準」や「数値」にしてやれば治ります。

maruru01 · Answer

>「--」って何ですか？
TEXT関数は、YEARやMONTHやDAYと違い、文字列を返します。
実際に、「--」を取った数式にすると、左揃えの文字列が表示されます。
もちろん、見た目は同じだし、横位置も設定し直せばいいのですが、最初から数値データに変換しておけば、右揃えになるので、月や日のセルと揃います。
その文字列→数値に変換する役割を持つのが、「--」の部分です。
Excelでは、数値と認識出来る文字列を四則演算の中に入れると自動的に数値に変換してくれます。
（ただし、数値と認識出来ない場合はエラーになります。）
今回は和暦の数字なので、明らかに数値と認識出来ます。
で、ポイントは、値を変化させずに四則演算させるということです。
そして、「--」とは、「(-1)*(-1)」のことなのです。
これなら、値は変化しませんね。
要は、値が変化しなければいいので、

=TEXT(A1,"e")*1
=TEXT(A1,"e")/1
=TEXT(A1,"e")+0
=TEXT(A1,"e")-0

のどれでもよく、また数値に変換する関数を使用して、

=VALUE(TEXT(A1,"e"))

でもOKです。

ところで、年を出す数式で、

=YEAR(A1)-1988

という回答がありますが、これだと、1989/1/1～1989/1/7も「1」になってしまいます。
（実際は"昭和"64年）
なので、不完全な回答だと思いますよ。

maruru01 · Answer

こんにちは。maruru01です。

年を和暦で抽出する場合は、

=--TEXT(A1,"e")

で出来ます。
もちろん、平成だろうが昭和だろうが構いません。
月と日は他の方の通り、

=MONTH(A1)
=DAY(A1)

でいいと思います。

yomo3 · Answer

エクセルとアクセスでのデータ交換用の変更ですので、表示を変えるだけでは、ダメでしょうね。

B1に「=YEAR(A1)」、C1に「=MONTH(A1)」、D1に「=DAY(A1)」と入力すれば、数値としての値が得られます。
ただし、B1は西暦です。
どうしても年号でなければならないとしたら、
平成生まれの人のみであれば、
「=YEAR(A1)-1988」
昭和生まれの人もいるのであれば、
「=IF(YEAR(A4)>1988,YEAR(A4)-1988,YEAR(A4)-1925)」
というようにしましょう。
大正生まれの人がいる場合などは、IF関数の入れ子を増やせばよいだけなので、ご自分でお考えください。

moon00 · Answer

#1さんとは違うやり方で。
表示上ではなく、データとしてその数値が必要な場合。

まず、A列のデータが、「数値」なのか「文字列」なのかで
扱いが変わります。
数値で入っている場合は、「平成9年3月15日」の場合は
上の数式バーに「1997/3/15」という形で出てくると思います。
この場合は、B、C、D列にそれぞれ「=year(A1)」「=month(A1)」
「=day(A1)」と入力すれば取り出せます。
ただし、年だけは西暦となるので、その場合は工夫が必要ですが。
平成しかなければ「=year(A1)-1988」として下さい。
次に文字列で入っている場合は、「年」を取り出す場合
=LEFT(RIGHT($A1,LEN($A1)-FIND("成",$A1)),FIND("年",RIGHT($A1,LEN($A1)-FIND("成",$A1)-1)))
となります。
要は年を表す数字が「成」と「年」に挟まれているので、
その位置を探して、取り出しています。
「月」と「日」はこの式をC、D列にコピーして
「月」の場合は「成→年」「年→月」に
「日」の場合は「成→月」「年→日」にして下さい。

多分数値で入っているだろうから、こんな面倒くさいことは
しなくて済むと思いますが。（苦笑）

noriemon · Answer

ええとやり方は他にもあるかもしれませんが、、、
まず、B1,C1,D1のセルに　=A1　と入れてみてください。
そして、
B1の"セルの書式設定"→"表示形式"タブを選択し、
"分類"→"ユーザ定義"にし、
"種類(T):"のところに現在[$-411]ggge"年"m"月"d"日"　と入力されていると思います。
これを　e　と入力してください。
すると、サンプルが　9と表示されると思います。

同様に表示形式を
C1には　m　を
D1には　d　を
入力してみてください。
どうですか？

エクセルで生年月日の数字のみを別々の列に取り出すには…？

>A1が平成9年7月11日で B1に数式を入力すると

maruru01さん。

>「--」って何ですか？

こんにちは。

エクセルとアクセスでのデータ交換用の変更ですので、表示を変えるだけでは、ダメでしょうね。

この回答への補足

#1さんとは違うやり方で。

この回答への補足

ええとやり方は他にもあるかもしれませんが、、、

この回答への補足

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>A1が平成9年7月11日で　B1に数式を入力すると