Is there a way to convert "E+02" to text automatically?

[drose9]

Hi,

I have a recurring issue in excel in which when I attempt to import a set of data that has the text "E-00" at the end, it converts to "E+02" or "E+03" automatically. If you know of an easy way to fix this issue, it would be very helpful.

My current method of fixing it is to:

• Insert a column
• Filter the items by E+
• Sort A to Z so the "E+03"s are at the bottom
• Enter ="0"&cell&"E-00" for the "E+02" items and =cell&"E-00" for the "E+03" items
• Unfilter
• Sort on the new column A to Z
• Copy/paste values into the original item list over the incorrect items.

This method works, but takes a long time and is something I have to do daily for several reports.

Example of data:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Desired Item[/TD]
[TD]Imported Item[/TD]
[/TR]
[TR]
[TD]0123E-00[/TD]
[TD]123E+02[/TD]
[/TR]
[TR]
[TD]0875E-00[/TD]
[TD]875E+02[/TD]
[/TR]
[TR]
[TD]1416E-00[/TD]
[TD]1416E+03[/TD]
[/TR]
[TR]
[TD]0875E-00[/TD]
[TD]875E+02[/TD]
[/TR]
</tbody>[/TABLE]

*Other, non effected item codes are also included in the list

Please let me know if you need any clarification on the issue.

Thanks,
D

 

[oldbrewer]

have you looked at find "E+??" replace with "E+00"

 

[oldbrewer]

ignore last post - your first imported item is actually the number 12300 which is 1.23 X 10^4 so you can only get 1.23 x 10^2 by
dividing by 100

 

[shg]

Import the field as text.

 

[oldbrewer]

why do you want 00 on the end anyway

 

[Rick Rothstein]

why do you want 00 on the end anyway

I was kind of wondering that too, especially since that change physically alters the values and not all in the same way. For example, this one...

[table="width: 200"]
[tr]
[td]Desired[/td]
[td]Imported[/td]
[/tr]
[tr]
[td]0875E-00[/td]
[td]875E+02 [/td]
[/tr]
[/table]

effectively divides the original value by 100 whereas this one...

[table="width: 200"]
[tr]
[td]Desired[/td]
[td]Imported[/td]
[/tr]
[tr]
[td]1416E-00[/td]
[td]1416E+03[/td]
[/tr]
[/table]

effectively divides the original value by 1000.