エクセルで2.01E+04表示を201E2に変換するのは？

大量のデータを処理しなくてはならないのですが、ときどき送られてくるエクセルでデータに使われるEを含んだ記号文字列が数値になり、しかも下記のような表示に化けてしまってます。()内は数値です。

201E2⇒2.01E+04（20100）
803E7⇒8.03E+0.9（8030000000）
903E9⇒9.03E+11（903000000000）

最初から文字列で入れてくれればこんなことにはならないと思うのですが、お客様あってのことなのであまり強くは言えません。やむをえず一つずつ調べてこちらで修正をしています。

これを関数あるいはVBAで一括変換する方法はないでしょうか？

No.8 ベストアンサー 回答者： BLUEPIXY 回答日時： 2005/04/28 20:11

>=LEFT(A1,3)&"E"&LEN(""&A1)-3

>式の意味はわかりますが、なんでこれで変換できるのかわかりません。教えていただけますか？
指数表示になっていても、（内部的には？）質問文のカッコの中の数字になっているので、
20100
8030000000
の様になっていると考えられます。
もともとのフォーマットから数値として有効なのは最初の３桁だけなので、
LEFT(A1,3)で文字列と見立てて最初の３文字を取り出せます。
あとは、付けられた０の数を数えれば良いのですが、
201E2→20100の様に、数値の部分以外は０ですから、
全体の文字数から３文字を引けば、付けられた０の数が分かります。

この回答へのお礼

なるほど、ありがとうございました。

お礼日時：2005/04/29 09:10

No.13 回答者： n_and_n 回答日時： 2005/04/29 23:02

ANo.#12 の「お礼」に対して。

やはり何処かでTextデータをExcelに変換させていたようですね。　相手先の入力は恐らく、201E2 のようになっているのでしょう。

今後もこのデータを受け取り、こうした変換を続けるようであれば、相手先に事情を説明して、CSV形式で貰える様にされた方が、お互いに合致するデータを保有することで、健全な解決法だと思います。

ちょっと、立ち入りすぎましたか？　失礼。

この回答へのお礼

そうですね、今後のことを考えると、小細工をするより「健全な解決法」を目指したほうが良いのだとは思います。
努力してみます。

ご親切にありがとうございました。

お礼日時：2005/04/30 18:18

No.12 回答者： n_and_n 回答日時： 2005/04/29 13:33

このコンテンツは色々と教えてもらうことがありました。

ANo.#1：
　　A1=2.01E+04
　　B1=INT(LOG(A1))-2
　　C1=TEXT(A1/(10^B1),"0")&"E"&B1
　演算子「&」は文字列データでなくても連結できてしまう。（セルB1は数値データである。）
　（セルA1は数値20100でも、文字列201E2、2.01E4のいづれであっても、B1、C1は正しく計算される。）
　　
ANo.#2：
　=LEFT(A1,3)&"E"&LEN(""&A1)-3
　文字列関数 LEFT、LEN の引数に、文字列データでないデータを指定できる。

ANo.#3：
　=LEFT(TEXT(A1,"0"),3)&"E"&LEN(TEXT(A1,"0"))-3
　ANo.#2の例から、TEXT関数で変換する必要は無い。

ANo.#4：
>それって、Textファイルとか、CSVとかではありませんか？
　わたしもそう思う。エクスプロラーでCSVファイルをダブルクリックしたり、Excelのメニューバー[ファイル]-[開く]
　とかツールバー「開く」ボタンでCSVファイルをExcelに読み込ませると元が201E2となっていても
　2.01E+04と表示されてしまう。
　Excelのメニューバー[データ]-[外部データの取り込み]-[テキストファイルのインポート]で該当する列を
　文字列に指定すればこの問題は生じない。

　Sub 指数変換()について
　c.Value = Format(x / (10 ^ (Int(x) - 2)), "0") & "E" & Int(x) - 2
　は　　　　　　　　　　　↑
　c.Value = Format(c.Value / (10 ^ (Int(x) - 2)), "0") & "E" & Int(x) - 2
　の間違いでしょう。

ANo.#5：
　Function 指数形式を文字列変換()　を　Sub 指数形式を文字列変換()、End Function　を　End Sub　と直すと
　動いた。でも、2.01E+04 が文字列データだと勘違いしている、201E2 にも変換されない。　回答になっていない。

ANo.#6：
　=LEFT(A1,FIND("E",A1)-1)*POWER(10,RIGHT(A1,LEN(A1)-FIND("E",A1)))
　これも2.01E+04 が文字列データだと勘違いしている。文字列2.01E+04が20100となるだけ。　論外。

ANo.#7：無視

ANo.#8：略

ANo.#9：
　buf = Application.Substitute(Format$(c.Value, "000E+0"), "+", "")
　表示形式を"000E+0"と直すことがオーソドックスで良いと思う。　

ANo.#10：略

総括：
　ANo.#4の「お礼」にあるmerlionXX さんのSub 指数変換()とANo.#9のコードを結合したものが一番使い易いと感じます。

この回答へのお礼

くわしく解説していただき、ありがとうございました。
> c.Value = Format(c.Value / (10 ^ (Int(x) - 2)), "0") & "E" & Int(x) - 2
　の間違いでしょう。

はい、ご指摘の通りでした。

それから元データはCSVやtxtじゃありません。
ホストコンピュータ（？）から取り出したデータをエクセルとして送付されてくるものです。

お礼日時：2005/04/29 20:51

No.11 回答者： Wendy02 回答日時： 2005/04/29 13:06

恥ずかしい間違いをしてしました。

ご指摘のとおり、rtn は、場所はどこにも確保されていません。それは、bufですから、

　If Not IsError(rtn) Then
は、
　If Not IsError(buf) Then
で、
buf は、Variant型になりますね。
失礼しました。（^^;

この回答へのお礼

わざわざ訂正ありがとうございます。
やっとわかりました。

お礼日時：2005/04/29 20:46

No.10 回答者： Wendy02 回答日時： 2005/04/29 09:36

＞If Not IsError(rtn) Thenは "rtn" がエラーじゃない場合って意味ですよね？

＞"rtn"って何ですか？

最初に、私のコードには、変数の宣言が抜けていますね。（^^;
Dim rtn As Variant 　

それで、英語の"return" の省略形で、「戻り値」のつもりです。
If で、「+」が入っているかチェックしているのだから、エラーがあるはずはないのですが、念のために入れました。これは、１つのパターンですからね。

変数に入れられるものは、Substitute の戻り値とエラーの二種類のタイプが入ります。だから、Variant型になります。

なお、みなさん、関数で処理されているのに、元の場所に、上書きしてよいのかなって、後で気がとがめたのですが、悪ければ、Offsetや、他のシートに写してくださいね。

この回答へのお礼

ほんとに何度もありがとうございます。

変数の宣言のことじゃなくて、rtnにはどこで代入されているのか疑問なんです。
Substitute の戻り値ならbufじゃないんですか？
よくわかってないのにすみません。

お礼日時：2005/04/29 11:50

No.9 回答者： Wendy02 回答日時： 2005/04/28 20:30

merlionXXさん、こんなのどうですか？

Logを使うよりも、元が文字列だったら、Excelが自動キャストした過程を考えて、戻したほうが、手っ取り早いような気がしますね。もしも、本当に指数表示だったら、責任は持てないけれどね。（^^;

Sub Exponent2Text()
Dim Rng As Range
Dim c As Range
Set Rng = Selection　'マウスで選択
Dim buf As String
For Each c In Rng
With c
　If InStr(.NumberFormatLocal, "E+0") > 0 Then
　　　buf = Application.Substitute(Format$(c.Value, "000E+0"), "+", "")
　　　If Not IsError(rtn) Then
　　　.NumberFormatLocal = "@"
　　　.Value = buf
　　　End If
　End If
　End With
Next c
End Sub

この回答へのお礼

ほんとうにいつもありがとうございます。

なあるほど、こうやればいいんですね。
最後に一つだけ教えてください。

If Not IsError(rtn) Thenは "rtn" がエラーじゃない場合って意味ですよね？
"rtn"って何ですか？
何度もすみません。

お礼日時：2005/04/29 09:20

No.7 回答者： ja7awu 回答日時： 2005/04/28 19:07

あれっ、 この質問、タイトルと本文の内容が逆になっていますね。

的外れだったら無視してね。

この回答へのお礼

タイトルと本文の内容が逆になっていますか？

本来、201E2と入力したのに、そのセルの中は数値で20100に変わり、これが表示上では2.01E+04となっているものを、もとの201E2に変換というか復元したいということです。

お礼日時：2005/04/29 09:08

No.6 回答者： ja7awu 回答日時： 2005/04/28 19:02

=LEFT(A1,FIND("E",A1)-1)*POWER(10,RIGHT(A1,LEN(A1)-FIND("E",A1)))

指数表示(2.01E+04)の場合は、書式を指数にして、少数点以下の数を 2 に設定。
数値表示の場合は、書式を数値に設定。

で如何ですか。

この回答への補足

エラーになってしまいました。

補足日時：2005/04/29 09:08

No.5 回答者： kuromoomin 回答日時： 2005/04/28 18:53

ＶＢＡで作ってみました。

(分りやすく作ったつもりです）
適当にアレンジすれば、使えると思います。


Function 指数形式を文字列変換()
Dim strSs As String
Dim StrMoji As String
Dim inCom As Integer
Dim inPlus As Integer

'まず、数値を指数形式で文字列に格納します。
strSs = Format(ActiveSheet.Cells(1, 1).Value, "0.00E+00")
'コンマの位置を調べます
inCom = InStr(1, strSs, ".", vbTextCompare)
'プラスの位置を調べます
inPlus = InStr(1, strSs, "+", vbTextCompare)
'コンマとプラスを除いて、文字列を取り出す。
StrMoji = Left(strSs, inCom - 1) & _
Mid(strSs, inCom + 1, inPlus - inCom - 1) & _
Mid(strSs, inPlus + 1, Len(strSs) - inPlus)
'セル書式を文字列に。
ActiveSheet.Cells(1, 1).NumberFormatLocal = "@"
'もとの位置に戻す
ActiveSheet.Cells(1, 1).Value = StrMoji

End Function

この回答へのお礼

ありがとうございます。
Sub Functionの使い方がよくわかりません。

そのため試すことができませんでした。
すみません。

お礼日時：2005/04/29 09:02

No.4 回答者： Wendy02 回答日時： 2005/04/28 18:29

merlionXXさん、こんにちは。

＞最初から文字列で入れてくれればこんなことにはならないと思うのですが、この質問は良く見ますね。
ちょっと確認ですが、もしかしたら、それって、Textファイルとか、CSVとかではありませんか？それとも、相手の方が、Excelの標準方式で直して送ってきているとか？
ただ、関数で解決できるようだったら、私はパスしますが、VBAでも考えてみたいと思います。

この回答へのお礼

Wendy02さん、いつもお世話になりありがとうございます。
ええ、作業の途中段階なのでVBAで処理したいです。
No1さんに教えていただいた関数をもとに以下のようなVBAを書きました。ただ、学生時代から数学が苦手で、LOGなんて何がなんだかさっぱりわからず、あってるかどうか自信がありません。見ていただけたら幸いです。

Sub 指数変換()
Dim c As Range
For Each c In Range("D:D")
If c.NumberFormatLocal = "0.00E+00" And IsNumeric(c) Then
x = Application.WorksheetFunction.Log(c.Value, 10)
c.NumberFormatLocal = "@"
c.Value = Format(x / (10 ^ (Int(x) - 2)), "0") & "E" & Int(x) - 2
End If
Next
End Sub

お礼日時：2005/04/28 19:08