エクセルの指数化け！？

Question

いま大変困っています。
会社のホストコンピュータからエクセルにデータをダウンロードをします。
これはエクセルでファイルが出力されるので最初からエクセルに書式を設定はできません。
そのデータのうち、おびただしい契約のコード（５桁）があるのですが、
たとえば

10A15
1G123
20B25
3013F
3A555
AX121　のようなもので、英数半角です。（数字だけもあります。）

通常は何も問題がないのですが、途中にEが入ると、

201E7→2.01E+09
2E137→2.00E+137
1E231→1.00E+231
10E75→1.00E+76
101E8→1.01E+10

のように文字化け（指数化け）してしまいます。
わたしの仕事は、この5桁のコードを、先頭の2桁と後ろの3桁に分けたいのですが、指数からではできません。
エクセルでなくCSVなどのテキスト形式で出力してもらえばいいのでしょうが、かなり時間がかかりそうで、当面は化けたエクセルから判読するほかなさそうです。
何か良い方法はないでしょうか？
関数でも、VBAでもかまいません。
お助けください。

nda23 · Accepted Answer

論理を組み立てます。

(1)データが非数値である。
　そのままでよい。
(2)データが数値である。
　(2-1)全部数字である。（指数がない）
　　そのままでよい。（先頭が0にならないから)
　(2-2)指数と誤認（"E+"がある）
　　(2-2-1)２桁目が"E"
　　　例：1E001
　　(2-2-2)４桁目が"E"
　　　例：123E1
　　(2-2-3)特例
　　　例：1E003と100E1
　　　どちらも1.00E+03になる。
　　　（2-2-3-1)判定可能
　　　　123E1→1.23E+03
　　　　100E0→1.00E+02
　　　　100E9→1.00E+11
　　　　よって、小数部が"00"でなければ4文字目が"E"、
　　　　指数が2～11でなければ必ず2文字目が"E"です。
　　　（2-2-3-2)判定不可能
　　　　上記以外、つまり小数部が"00"で指数が2～11の
　　　　場合は判断できませんので、問合せが必要です。

Const 契約列 = 1
Dim 行 As Long
Dim 位 As Long
Dim 字 As String
Dim 指 As Long
Dim 小 As String
Dim 値 As Variant
Do
　　行 = 行 + 1
　　値 = Cells(行, 契約列).Value
　　If IsEmpty(値) Then Exit Do
　　If VarType(値) = vbDouble Then
　　　　字 = Cells(行, 契約列).Text
　　　　位 = InStr(字, "E+")
　　　　If 位 = 0 Then
　　　　　　字 = "'" & 字
　　　　Else
　　　　　　小 = Mid(字, 位 - 2, 2)
　　　　　　指 = Val(Mid(字, 位 + 2))
　　　　　　If 小 <> "00" Then
　　　　　　　　字 = "'" & Left(字, 1) & 小 _
　　　　　　　　　 & "E" & CStr(指 - 2)
　　　　　　Else
　　　　　　　　If 指 < 2 Or 指 > 11 Then
　　　　　　　　　　字 = "'" & Left(字, 1) _
　　　　　　　　　　　 & "E" & Format(指, "000")
　　　　　　　　Else
　　　　　　　　　　'★分からないのでそのまま
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　　　Cells(行, 契約列).Value = 字
　　End If
Loop

end-u · Answer

>=AND(A1>99,A1<900000000001,LEN(SUBSTITUTE(A1,0,""))=1)
>でのチェックでは誤変換はすべてTRUEのフラグがたってくれましたが、正しく変
>換されたもののなかにもTRUEがけっこうありますね。
>これはどのようなちぇっくなのでしょう？

誤変換というより、判別できない契約コードが以下

1E002　100E0　1.00E+02　100
1E003　100E1　1.00E+03　1,000
1E004　100E2　1.00E+04　10,000
1E005　100E3　1.00E+05　100,000
1E006　100E4　1.00E+06　1,000,000
1E007　100E5　1.00E+07　10,000,000
1E008　100E6　1.00E+08　100,000,000
1E009　100E7　1.00E+09　1,000,000,000
1E010　100E8　1.00E+10　10,000,000,000
1E011　100E9　1.00E+11　100,000,000,000
2E002　200E0　2.00E+02　200
2E003　200E1　2.00E+03　2,000
2E004　200E2　2.00E+04　20,000
2E005　200E3　2.00E+05　200,000
2E006　200E4　2.00E+06　2,000,000
2E007　200E5　2.00E+07　20,000,000
2E008　200E6　2.00E+08　200,000,000
2E009　200E7　2.00E+09　2,000,000,000
2E010　200E8　2.00E+10　20,000,000,000
2E011　200E9　2.00E+11　200,000,000,000
3E002　300E0　3.00E+02　300
3E003　300E1　3.00E+03　3,000
：
：
9E010　900E8　9.00E+10　90,000,000,000
9E011　900E9　9.00E+11　900,000,000,000

90組あります。
>正しく変換されたもののなかにもTRUEがけっこうありますね。
正しく変換されたのか、誤変換なのか判別できない、という事です。

>最初に、「これはテキストファイルではない」というメッセージが出て、それで
>も開くと指数化けどころか文字化けしてしまいます。
テキストファイルの取り込みではなく、[外部データの取り込み]-[新しいデータベースクエリ]のほうです。
データベース種別で Excel Files を選択します。

>会社のシステム担当に事情を話して変更依頼をしましたが、...
シングルクォートで囲んでセットするか
頭にプリフィックスとしてシングルクォートをつけるかなんでしょうけど
詳細わからないのでまあ、諸問題あるのでしょう。
クエリで直接引っ張るのもダメなんでしょうね。

nda23 · Answer

#5です。
編集した文字列を戻すのを忘れていました。

End If
　　　　Cells(行, 番号列) = 字 '編集した文字列をセルに戻す
Loop

nda23 · Answer

先頭が0でないなら、全桁数字はそのままでよく、
後3桁に英字が来ないなら、以下のようにします。

Const 番号列 = 5
Dim 行 As Long
Dim 字 As String
Dim 位 As Long
Do
　　　　行 = 行 + 1
　　　　字 = Cells(行, 番号列).Text
　　　　If 字 = "" Then Exit Do
　　　　位 = Instr(字, "E+")
　　　　If 位 = 0 Then
　　　　　　　　If VarType(Cells(行, 番号列).Value) <> vbString Then
　　　　　　　　　　　　字 = "'" & 字
　　　　　　　　End If
　　　　Else
　　　　　　　　字 = "'" & Replace(Left(字, 位), ".00", "") _
　　　　　　　　　　+ Format(Val(Mid(字, P + 位)), "000")
　　　　End If
Loop

end-u · Answer

1E003　→　1.00E+03

100E1　→　1.00E+03

指数で表示されるデータ内に、こういうケースはありますか？
つまり、"E"が2桁目にくる事もあるし4桁目にくる事もある？

指数表示されるのが、必ず2桁目に限られてるなら
　=SUBSTITUTE(TEXT(A1,"0E+000"),"+","")
これだけで済むのでしょうけど。

4桁目にくる場合もあるとしたら、
=IF(CELL("format",A1)<>"S2",
　　A1,
　　IF(MID(TEXT(A1,"0.00E+00"),3,2)="00",
　　　　SUBSTITUTE(TEXT(A1,"0E+000"),"+",""),
　　　　LEFT(TEXT(A1,"0.00E+00"),4)*100&"E"&RIGHT(TEXT(A1,"0.00E+00"),2)-2
　　)
)

ただ、
>1E003　→　1.00E+03
>100E1　→　1.00E+03
これは判別できませんから、別セルに
=AND(A1>99,A1<900000000001,LEN(SUBSTITUTE(A1,0,""))=1)
として TRUE のデータだけ要チェックです。

でも、できる事なら
>会社のホストコンピュータからエクセルにデータをダウンロードをします。
ここから見直さないと厳しいでしょうね。
その契約コード自体がインデックスキーでなければ
インデックスと契約コードのみテキストデータで吐き出してもらって変換表を作っておくとか。
または、試しに
エクセルファイルを開かずに[外部データの取り込み]やってみるとか。

mike_g · Answer

[No.2お礼]へのコメント、

》 下3桁がアルファベットで始まることもありません。
》 つまり頭から3番目は必ず数字です。

質問文で示されたデータの
10A15
20B25
10E75
は、上の「規則性」に反していませんか？

kagakusuki · Answer

回答番号ANo.1です。
　申し訳御座いません、例え、表示形式に違いが現れる場合であっても、判別が出来なくなる場合゛御座いました。
例.

00001→1
001E0→1
01E00→1
1E000→1

00010→10
010E0→10
001E1→10
10E00→10
01E01→10
1E001→10

従って、表示形式が、元のコードに従って変化する場合と、変化しない場合の、どちらであっても、必ず元のコードがどれであるのかを、判別する事が出来なくなる場合がありますので、指数に変換されてしまったコードの中の一部は、既に元のコードの情報が壊れてしまっているものと考えられます。
　ですから、「化けたエクセルから判読」するのは危険という事なりますから、早急にダウンロードの方法を見直されると共に、過去の出力済みのコードも、何処がどの様に間違えてしまっているのかを、確認された方が、宜しいかと思います。

kagakusuki · Answer

確認したいのですが、例えば「10E03」や「90E03」の様に、「[一桁の数字]+[0E03]」の場合は、「10000」、「1.0E+04」、「1.00E+04」の内の、どの表示となるのでしょうか？

もし、「10000」と表示される場合や「1.00E+04」と表示される場合には、

10000→10000 又は 1.00E+04
100E2→10000 又は 1.00E+04
10E03→10000 又は 1.00E+04
1E004→10000 又は 1.00E+04

と変換されてしまうのですから、これらを区別する事は出来ず、元のコードがどれであったのかを判断する事は不可能です。
　これがもし、

10000→10000
100E2→1.00E+04
10E03→1.0E+04
1E004→1E+04

という様に、表示形式に違いが現れるのであれば、次の様な関数で、元のコードに再変換する事が出来ます。（以下の関数は、指数に変換されてしまったコードが、A1セルに存在している場合の関数です）

=IF(LEFT(CELL("format",A1),1)="S",LEFT(A1,REPLACE(CELL("format",A1),1,1,)+1)&"E"&TEXT(INT(LOG10(A1)),REPT(0,3-REPLACE(CELL("format",A1),1,1,))),A1&"")

尚、この関数は、表示形式に違いが現れる場合に対して特化していますので、表示形式に違いが現れない場合には、指数を正しく変換する事は出来ません。

エクセルの指数化け！？

論理を組み立てます。

>=AND(A1>99,A1<900000000001,LEN(SUBSTITUTE(A1,0,""))=1)

#5です。

先頭が0でないなら、全桁数字はそのままでよく、

1E003 → 1.00E+03

この回答への補足

[No.2お礼]へのコメント、

回答番号ANo.1です。

確認したいのですが、例えば「10E03」や「90E03」の様に、「[一桁の数字]+[0E03]」の場合は、「10000」、「1.0E+04」、「1.00E+04」の内の、どの表示となるのでしょうか？

似たような質問が見つかりました

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

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

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

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

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

1E003　→　1.00E+03

　回答番号ANo.1です。

　確認したいのですが、例えば「10E03」や「90E03」の様に、「[一桁の数字]+[0E03]」の場合は、「10000」、「1.0E+04」、「1.00E+04」の内の、どの表示となるのでしょうか？