Scientific Notation Issue E+000 etc.

[BurghRocks]

Hi all,

I have a large list of numbers in column A, about 60k. Scattered throughout
that list are numbers that Excel read as formulas since the fields were not
converted to text ahead of time. I can have this list of numbers recreated
again and this time in the correct format so that I should not have this
problem again. What I need to do though is identify which of the numbers
have been "fixed". Is there a way to take my new list and put it next to the
old one and then run some kind of compare function that will allow me to
isolate the data that has been fixed? I believe there are at least 1000 so
I'd prefer not to do it manually.

Thanks in advance for any guidance.

 

[Sheeloo]

If you know the format then you can use something like
=TEXT(A1,"Format")=B1 to get TRUE or FALSE
where Format is the format to convert A1 into the text format of B1

 

[BurghRocks]

Hi Sheelo,

Would there be a way just to identify the numbers in column A that have the
"+" in them and then only return the data in column B when the value in
column A has the "+"?

Thanks,
Bill

 

[Sheeloo]

You can try this
1. Press CTRL-'
' is on the same key as ~ on the top left corner of the keyboard.

2. Select Col A

3. Choose Edit|Replace
and replace = with '=

4. Press CTRL-' again

All cells with a formula in them will now have a = in the first place...
You can then filter on 'Starts with ='

 

[BurghRocks]

Thanks Sheeloo, I'll give that a shot and see if it works. Seems like a
decent workaround.

Thanks,
Bill

 

[Gord Dibben]

Excel will change any number with more than 11 digits to Scientific
Notation.

Enter in B1 =LEN(A1)

Double-click the fill handle to copy down.

Filter on column B for greater than 11 to reveal those numbers with
Scientific Notation.


Gord Dibben MS Excel MVP