Numbers E Numbers

[HenkdeBruin]

Hi there!

Quick question regarding the 'e' function that Excel seems to apply automatically.

I have +/- 3000 rows of data, which all have an unique indentification-number. Some of them exist out of 8 numbers, others contain 5 numbers, then a letter, and then two more numbers. The problem now, is that some of these letters are an 'e'. Excel interpets this as "First 5 numbers times E to the power of two numbers", which (of course) causes problems for me.

Is there a way to disable this option? I think I know my way around a formula that I can apply to work around it (Some thing like IF(FIND("E";A1)=TRUE;CONCATENATE(LEFT(A1;5);"E";RIGHT(A1;2);A1) (assuming the code is in A1) I guess), but if course it would be a lot easier for me to simply disable this option.

Does anyone know if it is possible, and if so how to do this.

Thanks in advance!

Henk

[dflak]

There are two options. Sometimes the "E" is imposed because the column is too narrow. Widen the column and it displays the full number. Or you can format the column as text. If the number is simply an ID number and not used for math, this is OK.

[MarvinP]

Hi Henk and welcome to the forum,

If you format the column as TEXT before you enter you ID's they should not try to convert to numbers. After Excel has guessed they are numbers, it will be a little late to change them back.

Or read:

https://support.microsoft.com/en-us/...ormat-in-excel

[HenkdeBruin]

Hi,

Thanks for your replies.

Problem is that I get the codes from an online database, and I have to download it as an Excel-sheet. So I can't apply the "format before insert" strategy.

@dflak, Thanks for your reply too, but what you suggest seems not to be the case (see image)
Untitled.png
When I click on the cell the scientific notation is already there, while I want the code to be 79412E10 in this case.

Hope you can help me.

Cheers!

[Pete_UK]

If the numbers are already in scientific format, then I think you will need to use a helper column to convert all the entries to text, then you can fix the values and copy/paste them over the original data. For example, you can use this in, say, H1:

=IF(LEN(B1)=8;""&B1;SUBSTITUTE(SUBSTITUTE(B1;"E+";"E");",";""))

Copy down as required, then fix the values and copy back into column B.

Hope this helps.

Pete

[Pete_UK]

Sorry, you will need to change that to this formula:

=IF(LEN(B1)=8;""&B1;SUBSTITUTE(SUBSTITUTE(TEXT(B1;"0,0000E+00");"E+","E");",";""))

then copy down etc.

Hope this helps.

Pete

[dflak]

I have this same issue since I work with wireless device ESNs. What number do you see in the formula bar when you select the cell? If you see an "E" notation then there is an issue. If you see the whole number, then select the formula bar and press ENTER and the number should then appear in the cell. A combination of highlighting the column and pressing F2 / F9 may make it happen. Otherwise you might be able to force something with a helper column for example =A2 & "" to force the number to a string.

I assume you do not have access to the database except through the reporting mechanism. Otherwise, you can define data type on import. Also, do you have the opportunity to download as a CSV file instead?

Pete, the problem with doing the conversion the way you describe is loss of precision. 123456781 and 123456782 Both work out to 12345678 E01.

[Pete_UK]

Yes, but the OP did say he has 8-character codes, either 8 all-digit, or 5-digit plus a letter plus 2-digit.

Pete

[HenkdeBruin]

Hi Pete_UK,

Thank you for your suggestion! For as far as I can see, it does exactly what I want. However there is a small little discomfort with it (sorry to be such a pain in the A$S (Attempt at Excel-humor )).

I inserted the formula and applied on the imaginary string of 55440E10 and it resulted in 55440E14, which is obviously really close to what I need, but not exactly the same.

If I adjust if to a LEFT(IF(LEN(B1)=8;""&B1;SUBSTITUTE(SUBSTITUTE(TEXT(B1;"0,0000E+00");"E+","E");";";""));7)&0, do you think this will be a feasable sollution? Or do you have a more logical/better solution?

Thanks for your help so far! I really appreciate it!

Henk

[Pete_UK]

The easiest thing is for you to try it out - I got the same result as you, i.e. 5.54E+14 when I entered the number, and then the extracted value was the same as yours.

It's difficult for me to try these things out exactly as we use a full-stop in the UK to denote the decimal point and use commas instead of semicolons as list separators, so it's quite a bit of work to change your formulas to suit my regional settings or for me to post a formula which is suitable for your settings. In addition to that, we are using different versions of Excel, which might also introduce other variations.

Hope this helps.

Pete