Dag-nab it! It does not work for ALL. But it did work for some! Here is a
short list of our ID's.
If you plop them in a spreadsheet they will change to scientific. Then you
will see how your formula works for some but not others.

423E97
424E02
419E02
419E06
419E07
409E04
409E50
401E05
401E53
402E50

"Sheeloo" wrote:

Actually I tried to be too smart but fell short :-(

Do your numbers have an alphabet at the third position for ALL ids?

If yes then second condition is not required...
try
=IF(ISNUMBER(A1),LEFT(A1,3)&"E"&(LEN(A1)-3),A1)

This will fail if your ids are numbers...

Pl. send the file with just the Ids to me if this also fails...

click on my name and follow instructions to get my id... :-)

"Plukey2" wrote:

Thanks for trying again but ... Nope
The ID number is 424E02
Excel opens it as 4.24E+04
Your formula converts it to 42400

You are a gem for trying. So far 4 of us in my company is stumped. We
tried all kinds of things.

"Sheeloo" wrote:

Sorry for giving the wrong solution. I did not think it through...

Try this -
Assuming your numbers are in Col A then try this in B1 and copy down
=IF(AND(ISNUMBER(A1),A1999999),LEFT(A1,3)&"E"&(LE N(A1)-3),A1)

This might work for you...for IDs upto 6 characters...


"Plukey2" wrote:

That was the simple thought for a bunch of us. If you highlight the column
and do Data then Text to Column... 4.19E+09 becomes 4190000000. That
doesn't work

We get data into Excel from Multiple sources (including outside sources).
We have LOTS of programs that write to the spreadsheet. It would take LOTS
of manpower to update all our inhouse programs and call ALL our outside
sources to have EVERY process write this column as text. We need excel to
stop thinking our ID's with "E's" are exponential numbers.

Thanks for answering so quick.

"Sheeloo" wrote:

How do you 'convert' data to Excel?

You need to define the column as TEXT while importing...

For data already imported -
Try selecting the column, choose Data|Text to column and choose TEXT as the
column type...

"Plukey2" wrote:

We transfer data from a database to Excel for multiple reasons. The ID to
our records are 6 characters such as "123A12" "456B12". When we have an ID
such as "419E07" Excel automatically changes that to 4.19E+09. A group of
us has tried EVERYTHING we know to stop this from happening. When we open
Excel, we DO NOT want Excel to convert. Any ideas?