EXCEL セル内の数値から、5桁目だけを抽出する方法について

質問

EXCELシートのセル内に、以下のような30桁からなる数値が入力されている
列が複数あります。

この列内の数値の右端から「5桁目」だけ、または左端から「10桁目」だけを
抽出したいと思っているのですが、EXCELの仕様で数値の先頭にある「０」は
表示されないため、各セルごとに表示されている数値の桁数はバラバラです。

数値の先頭に「０」が付いているセルが多数あり桁数が不揃いなことと、
数値が30桁もあるため、セルの数値表示が「1E+29」のように「E+29」で表示
されてしまっており、うまく数値を抽出することが出来ません。

◆元データ
　　「列１」　　　　
(1) 1234567890　 →（本当は30桁：000000000000000000001234567890）　　　
(2) 111111111100000000001111111111　　
(3) 1　　　　　　→（本当は30桁：000000000000000000000000000001）　
(4) 123456789012345678909999999999　

◆右端から5桁目だけを抽出する
　 「列１」　　　　　
(1)　 6　　　　　
(2)　 1　　　　　
(3)　 0　　　　　
(4)　 9　　

お手数お掛けいたしますが、ご指導下さいますよう宜しくお願い致します。

回答No.4

KURUMITO · ベストアンサー

解答Ｎｏ1です。
エクセルでは文字列で入力しない限りは、有効数字は15桁までです。そのために123456789123456789123456789のようにセルに入力したつもりでも123456789123456までがパソコンに取り込まれてそれ以上はどんなに入力しても0が付くだけです。
通常は先の数値では1.23457E+26と表示されますね。この数値の詳細を知るために「桁区切りスタイル」をクリックすれば123,456,789,123,456,000,000,000,000と表示され15桁以上入力してもそれ以上の入力は0が追加されて桁が多くなるだけのことです。
初めから文字列として15桁以上の数値を入力して、Ａ1セルにその全体が表示されている状態でしたら解答Ｎｏ1の式でも十分対応できます。
E+20とか表示されている場合には文字列ではなく数値ですから、数値の場合にはパソコンは有効数字を15桁までした取り込んでおりませんのでそれより下の桁までの数値で5桁目を表示させることは当然のこととしてできません。

お礼

KURUMITO 様
ご丁寧なご回答をありがとうございます。
お恥ずかしながら…
私はずっと、セルに「E＋20」などと表示されている意味が
分からず謎だったのですが…、EXCELは15桁以上の数値を
取り込みできない仕様と言うことが分かり、納得いたしました。
今後は、文字列として入力するようにしたいと思っております。

回答No.5

nagare

#2です
数値制限15桁に気づかずにすみません

データ入力後にセルの書式を変更してもダメですよ
セルの書式変更後、データ入力です
そうであればマクロでなくても、#1さん、#3さんの方法でいけます

ダメもとで指数付き数字を元に戻そうと思ったのですが、セルに入れたときに桁を捨てられてました
(#1さんの説明どおり)

111111111100000000001111111111
↓
1.111111111E+29
↓
1.111111111*(10**29)
↓
111111111100000000000000000000

>マクロを実行するには、VBA画面に上記記述をコピー＆ペーストすればよろしいでしょうか?
マクロをご存知と思ってました
参考までに

1)A1セルに 111111111100000000001111111111 が30桁表示するようにする
2)メニュー　ツール-マクロ-マクロ をクリック
3)マクロ名に mid5 を入力して 作成ボタンをクリック
4)Sub mid5()
　End Sub
　を
　書き換える
5)B1セルに =mid5(A1) を入れて実行
★メニュー　ツール-マクロ-セイキュリティー　で　中　にする必要があります

お礼

nagare 様
ご丁寧なご回答をありがとうございます。
マクロ初心者であることを申し上げておらず、大変失礼いたしました。
お蔭様で、希望通りの回答結果を得ることができました。

私はずっと、セルの書式設定で表示形式を「数値」に変更すると、
30桁表示に変更されているのに、数式バーには「E＋20」などと表示
されているれていたので、おかしいなぁと思っていたのですが…、
EXCELの仕様で15桁以上の数値を取り込みできず切捨てられると
言うことが分かり納得いたしました。
今後は、セルの書式変更後にデータ入力するように致しますね(*^_^*)

回答No.3

cistronezk

Excelの有効桁数は１５桁だそうです。それ以上の桁数の数字は、「文字列」として入力しないといけないようです。
http://support.microsoft.com/kb/269370/ja

文字列として入力した場合は、
=MID(REPT("0",30-LEN(A1))&A1,26,1)
で取得できます。

お礼

cistronezk 様
ご回答いただきまして　ありがとうございます。
お蔭様でEXCELの仕様ということが分かり安心しました。
通常のように手入力にて30桁の数値を入力したセルで、この数式を実行すると希望通りの結果を得ることが出来ましたが、今回使用しているデータは、EXCELの仕様のため「1.11110101110011E+29」のように表示されてしまっており、表示設定を「文字列」変えても「1.11110101110011E+29」のままでした。今回はEXCELの仕様のため、残念ながら希望通りの回答を得ることが出来ませんでしたが、とても勉強になりました。
ありがとうございました(*^_^*)

回答No.2

nagare

>セルの数値表示が「1E+29」のように「E+29」で表示されてしまっており

これが一番のネックなのですが、セルの書式(表示形式)を変えたらダメなのでしょうか？
標準→数値

また、４桁以下の場合は0でよろしいのでしょうか？

マクロで作ってみました
Function mid5(sNum As String) As String
mid5 = "0"
If 4 < Len(sNum) Then
mid5 = Mid(StrReverse(sNum), 5, 1)
End If
End Function

補足

nagare 様
ご回答いただきまして　ありがとうございます。
マクロを実行するには、VBA画面に上記記述をコピー＆ペーストすればよろしいでしょうか?　実行してみたのですが、マクロ名を付けて下さいと表示され、適当な名前を付けて作成ボタンを押下すると「コンパイラエラー」が表示されてしまいました。

なお、今回使用しているデータは、EXCELの仕様のため「1.11110101110011E+29」のように表示されてしまっており、セルの表示設定を「数値」に変更すると「111110101110011000000000000000」と表示されます。
上記の数値は15桁目以降は「0」になっているのですが、実際の数値は15桁目以降も何らかの数値が入力されているのです。
上記のことから、表示形式を「数値」に変えても、本来の数値表示にはなりませんでした。

回答No.1

KURUMITO

A1セルから下方にデータがあるとしてＢ1セルには次の式を入力し下方にオートフィルドラッグします。
=IF(A1="","",IF(LEN(A1)<5,0,LEFT(RIGHT(A1,5),1)*1))

お礼

KURUMITO 様
ご回答いただきまして　ありがとうございます。
通常の30桁の数値を入力したセルで、この数式を実行すると希望通りの結果を得ることが出来ました。
今回使用しているデータは、EXCELの仕様のため「1.11110101110011E+29」のように表示されてしまっており、セルの表示設定を「数値」に変更すると「111110101110011000000000000000」と表示されます。
上記の数値は15桁目以降は「0」になっているのですが、実際の数値は15桁目以降も何らかの数値が入力されているのです。
上記のことから、表示形式を「数値」あるいは「文字列」に変えてから、この数式を実行してみたのですが残念ながら希望通りの回答を得ることが出来ませんでした。