エクセルで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 · ベストアンサー

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

お礼

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

回答No.13

n_and_n

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

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

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

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

お礼

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

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

回答No.12

n_and_n

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

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じゃありません。
ホストコンピュータ（？）から取り出したデータをエクセルとして送付されてくるものです。

回答No.11

Wendy02

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

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

お礼

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

回答No.10

Wendy02

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

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

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

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

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

お礼

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

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

回答No.9

Wendy02

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"って何ですか？
何度もすみません。

回答No.7

ja7awu

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

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

お礼

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

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

回答No.6

ja7awu

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

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

で如何ですか。

補足

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

回答No.5

kuromoomin

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

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の使い方がよくわかりません。

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

回答No.4

Wendy02

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

回答No.3

ta123

"E"の前が3桁固定でしたら、以下の関数でできました。

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

（Excel2002で検証しました。）

お礼

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

No2の方のと同じ理屈ですね。
勉強になりました。