Scientific Notation convert to text

[Amileaux]

If I receive an excel spreadsheet where certain job ids have been converted to scientifc notation, is there a way to make them convert back to their original number? I understand that is I receive a .csv or .txt file I can import the field as "text" and this will not happen - but if someone has already brought it in and saved it this way as a .xls file is there a way to undo it (assume I have no recourse to the original .csv or .txt file). Thank you. Marie

 

[Mark W.]

Neither formula accomodates a job Id that starts with zero. For example:

021E102 comes across as 2.10E+103 When I apply Mark's function, it returns 2.1E+103 ...

It's always a good idea to include such cases in your sample data.

 

[Amileaux]

Nate - thank you for responding. I'm still wading through your code to try and understand why it is working in some cases and in others it is not - however, I am wondering about the "--" you placed in front of the "mid" function. It seems to remove the "" from around the number. I couldn't find this on the help menu (of course I'm probably asking for the wrong thing). Thanks. Marie

 

[NateO]

Bonjour Marie,

When you use string functions like left(), right() and mid(), the return is a string, even if it looks like a number. You can coerce numeric strings to real numbers in a variety of ways, use the n() function or mathematically operate on them in what would seem to be a moot manner, e.g.,

--x
x+0
x-0
x*1
x^1

The urinary minus takes operator priority in Excel, and there's been some discussion about it being more efficient than x+0.

The reason I'm using it is for your leading zero quandary, I want to see if the fourth char. is E or a number, operating on a text string, --E, returns an error, it effectively triggers the isnumber() function in the if() call.

Can you post some sample strings where my second function does not work properly?

=IF(ISNUMBER(--MID(B2,4,1)),LEFT(TEXT(B2,"#"),3)&"E"&TEXT(LEN(TEXT(B2,"#"))-3,"0##"),0&LEFT(TEXT(B2,"#"),2)&"E"&TEXT(LEN(TEXT(B2,"#"))-2,"0##"))

 

[DataWizard]

If I receive an excel spreadsheet where certain job ids have been converted to scientifc notation, is there a way to make them convert back to their original number? I understand that is I receive a .csv or .txt file I can import the field as "text" and this will not happen - but if someone has already brought it in and saved it this way as a .xls file is there a way to undo it (assume I have no recourse to the original .csv or .txt file). Thank you. Marie

Extremely annoying to me that Excel doesn't make this easier to the user. This is my 11 step workaround for columns of data that have long numbers mixed in with other data types.

Step 1	Copy/paste problem field into new sheet in column A
Step 2	Copy/paste Column A into text file
Step 3	Format Column B to text and paste text file into Column B
Step 4	In Column C, =IF(ISNUMBER(SEARCH("+",B1)),A1,"")
Step 5	Convert Column C into a number format with no decimals
Step 6	Copy/paste Column C into text file
Step 7	Format Column D into text and paste text file to Column D
Step 8	In Column E, =IF(IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2))=0,"",IF(IF(B2=A2,A2,D2)=0,A2,IF(B2=A2,A2,D2)))
Step 9	Copy/paste Column E into notepad
Step 10	Delete data in problem field and format column to text
Step 11	Copy/paste data from text file to problem field

<tbody>
</tbody>

 

[Rick Rothstein]

Can you post some sample strings where my second function does not work properly?

=IF(ISNUMBER(--MID(B2,4,1)),LEFT(TEXT(B2,"#"),3)&"E"&TEXT(LEN(TEXT(B2,"#"))-3,"0##"),0&LEFT(TEXT(B2,"#"),2)&"E"&TEXT(LEN(TEXT(B2,"#"))-2,"0##"))

If the original Job IDs can start with one or more zeroes, then there is no way to calculate the original Job ID from a number in scientific notation. Consider these Job IDs...

001E234
010E233
100E232

Each one will be converted to 1.00E+234 in scientific notation... there is no way to be able to decide looking at 1.00E+234 which of those three Job IDs were the original one.

 

[jsvanpel]

Try creating another column with:
=""&$A1
set column format to Text & Paste, Special, Value over the original scientific values.
dvp