エクセルの指数化け！？

質問

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

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でもかまいません。
お助けください。

回答No.8

nda23 · ベストアンサー

論理を組み立てます。
(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さまの数式を組み合わせた、
=IF(CELL("format",A1)<>"S2",A1,IF(AND(A1>99,A1<900000000001,LEN(SUBSTITUTE(A1,0,""))=1),"要目検",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)))

と、まったく同じ結果を返してくれました。
同じ指数に化けてしまうものはやはり1件ずつ手で調べるしかどうしようもないですね。

まだよくわかっていませんが、ご教示のコードを勉強させていただきます。
ありがとうございました。
また、行き詰ったら質問すると思いますので、その節は懲りずによろしくご指導をお願いいたします。

回答No.7

end-u

>=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 を選択します。

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

お礼

> 正しく変換されたのか、誤変換なのか判別できない、という事です。

その可能性があるのが90組ですかあ・・・。

> テキストファイルの取り込みではなく、[外部データの取り込み]-[新しいデータベースクエリ]のほうです。
> データベース種別で Excel Files を選択します。

やってみました。
やはり指数化けしていました。そして指数化けだけでなくたとえはA42B5のような完全に文字列のコードは消えていました。
残念、やはり会社を動かすしかなさそうです。
ただ会社は、わたしの人件費のほうがシステムを変えるコストより安い（というか残業もつけられないのでコスト０！）。
だから、多少時間がかかっても目検して手で修正しろといいたいようです。

とりあえずは、ご教示の数式をマクロ化して自動で変換させ、判読不能のものだけ目でみて修正を入れるようにします。
。・゜゜ '゜(*/□＼*) '゜゜゜

回答No.6

nda23

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

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

お礼

ありがとうございます。
ご教示のコードを、後ろの三桁にのみEがあるデータで試しても
残念ながら、

本来　→指数化　→変換後
201E7→2.01E+09→2.01E000
201E8→2.01E+10→2.01E000
203E9→2.03E+11→2.03E000

となってしまいました。

回答No.5

nda23

先頭が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

お礼

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

はい、その通りです。

> 後3桁に英字が来ないなら、以下のようにします。

いえ、残念ながら後3桁の先頭は必ず数字（0～9）ですが、後2桁には半角英数字なら何がくるかわかりません。

有難うございます。

回答No.4

end-u

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 のデータだけ要チェックです。

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

お礼

ありがとうございます。
ご教示の数式で
201E7
201E8
203E9
2E000
2E001
2E106
1E001
1E002
1E003
1E004
1E005
1E106
4E007
401E8
1E003
は正しく変換されました。

100E1
100E2
200E1
は誤変換されます。

=AND(A1>99,A1<900000000001,LEN(SUBSTITUTE(A1,0,""))=1)
でのチェックでは誤変換はすべてTRUEのフラグがたってくれましたが、正しく変換されたもののなかにもTRUEがけっこうありますね。
これはどのようなちぇっくなのでしょう？式の意味は、その範囲内で０を省いたら1桁になるものだと読めますが、よくわかりませんでした。

> その契約コード自体がインデックスキーでなければ

まさに契約コード自体がインデックスキーなのでお手上げです。
会社のシステム担当に事情を話して変更依頼をしましたが、金がない、時間がないのいってんばりで、いつ対応してくれるかもわかりません。

> または、試しにエクセルファイルを開かずに[外部データの取り込み]やってみるとか。

これは最初にためしました。
最初に、「これはテキストファイルではない」というメッセージが出て、それでも開くと指数化けどころか文字化けしてしまいます。
(T.T)うぇ～ん

補足

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

ありえます。
規則性といっても簡単で、
かならず英数半角で5文字のみ。
先頭は0にならない。（1～9またはA～Zがくる）
3番目はかならず数字。（0～9がくる）
2番目、4番目、5番目には。（0～9またはA～Zがくる）
というだけです。

回答No.3

noname#204879

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

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

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

お礼

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

すみません、おっしゃるとおりです。
このデータは指数化けの例を適当に作ったためで、作る際に規則性を失念していたためです。
実際にはこんなコードはありません。
大変失礼しました。

回答No.2

kagakusuki

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

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

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

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

お礼

00001→1
001E0→1
01E00→1

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

実は規則性があります。
頭1桁が0になることはありません。
下3桁がアルファベットで始まることもありません。
つまり頭から3番目は必ず数字です。

ですから上記の同一となる例で存在するのは

1E000→1.00E+00 と表示されます。
1E001→1.00E+01 と表示されます。

だけです。
なんとか判別出来ないでしょうか？

現在は指数化けしたものを目で探し、他のデータから検索して修正しており、非常に疲れます。

回答No.1

kagakusuki

確認したいのですが、例えば「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&"")

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

お礼

ありがとうございます。