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

質問

うまく説明できるか心配なのですが…どなたか教えてください。
　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，…
　と取り出したい?(分けたい?)のです。
というのも、アクセスのテーブルがこの形で入力するようになっていて、エクセルのワークシートにはすべてがつながって(スペースもなく)入力されているため、上記のようにできればアクセスに貼り付けやすいと考えました。どちらのソフトも詳しくはないので、アクセスのデータベースは変更したくないのです。
どなたか知恵をお貸しください。お願いします。

回答No.6

maruru01 · ベストアンサー

>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」だということです。

説明が長くなりましたが、結論としては、セルの表示形式を「標準」にすればいいということです。

回答No.7

yomo3

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

お礼

左隣の書式が引き継がれる→これも勉強になりました。
今までも、書式が変わっていて、目的の表示ができないことがありました。原因不明で困っていたのですが、列挿入が原因だったのかもしれません。これを知っておくことは、大切ですよね。ありがとうございました。

回答No.5

maruru01

>「--」って何ですか？
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年）
なので、不完全な回答だと思いますよ。

お礼

わかりやすく教えて頂いて本当にありがとうございます。初心者の私でも理解できたような気がします。
私のイメージでは
　「=TEXT(A1,"e")」は、ただ文字列を引っ張って来た　　　　　　　　　　　　だけなので、左揃えになる
　「=--TEXT(A1,"e")」は計算結果の数値なので、右揃　　　　　　　　　　　えになるということですか？
…今朝、違うテキストでみなさんの方法を復習したところこんな風になってしまいました。

A1が平成9年7月11日で　B1に数式を入力すると
なんと明治33年1月9日と結果が出てしまいました。
　maruru01さんが教えてくれた「--」を取ると、正確に「9」が表示されます。(ただし、左揃え。)
演算をさせると明治33年1月9日となってしまうんです。
これは、設定の問題だと思うのですが、どのように対処すればよいでしょうか。重ね重ねすみません。
この疑問は急いでいませんので、どなたかお暇なときに教えてください。

回答No.4

maruru01

こんにちは。maruru01です。

年を和暦で抽出する場合は、

=--TEXT(A1,"e")

で出来ます。
もちろん、平成だろうが昭和だろうが構いません。
月と日は他の方の通り、

=MONTH(A1)
=DAY(A1)

でいいと思います。

お礼

ありがとうございました。いろいろなやり方があるのですね。皆さんのやり方をやってみて、とても勉強になりました。ところで、=--TEXT(A1,"e") の「--」って何ですか？意味も知りたくなりました。お暇であれば教えてください。

回答No.3

yomo3

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

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

お礼

ありがとうございました。いろいろなやり方があるのですね。皆さんのやり方をやってみて、とても勉強になりました。「=IF(YEAR(A4)>1988,YEAR(A4)-1988,YEAR(A4)-1925)」 参考になりました。

補足

…今朝、違うテキストでみなさんの方法を復習したところこんな風になってしまいました。

A1が平成9年7月11日で　B1に数式を入力すると
なんと明治33年1月9日と結果が出てしまいました。
　演算をさせると明治33年1月9日となってしまうんです。
これは、設定の問題だと思うのですが、どのように対処すればよいでしょうか。重ね重ねすみません。
この疑問は急いでいませんので、どなたかお暇なときに教えてください。昨日はできたのですが…。

回答No.2

moon00

#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列にコピーして
「月」の場合は「成→年」「年→月」に
「日」の場合は「成→月」「年→日」にして下さい。

多分数値で入っているだろうから、こんな面倒くさいことは
しなくて済むと思いますが。（苦笑）

お礼

ありがとうございました。いろいろなやり方があるのですね。皆さんのやり方をやってみて、とても勉強になりました。ラッキーなことに「数値」で入力されていたので悩まなくてすみました。

補足

…今朝、違うテキストでみなさんの方法を復習したところこんな風になってしまいました。

A1が平成9年7月11日で　B1に数式を入力すると
なんと明治33年1月9日と結果が出てしまいました。
　演算をさせると明治33年1月9日となってしまうんです。
これは、設定の問題だと思うのですが、どのように対処すればよいでしょうか。重ね重ねすみません。
この疑問は急いでいませんので、どなたかお暇なときに教えてください。昨日はできたのですが…。

回答No.1

noriemon

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

同様に表示形式を
C1には　m　を
D1には　d　を
入力してみてください。
どうですか？

お礼

ありがとうございました。いろいろなやり方があるのですね。皆さんのやり方をやってみて、とても勉強になりました。

補足

…今朝、違うテキストでみなさんの方法を復習したところこんな風になってしまいました。

A1が平成9年7月11日で　B1に数式を入力すると
なんと明治33年1月9日と結果が出てしまいました。
　演算をさせると明治33年1月9日となってしまうんです。
これは、設定の問題だと思うのですが、どのように対処すればよいでしょうか。重ね重ねすみません。
この疑問は急いでいませんので、どなたかお暇なときに教えてください。昨日はできたのですが…。