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?

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?

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?

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?

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?

Ooops 424E2... Hey that is close. I need a 0 before that last 2 and we are
set. Wow! You are smart!

"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?

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?

Actually, I just need to change your &"E"& to &"E0"& and it works!!! Now
should I tell all my coworkers I figured this out? (Smile).. Just kidding...
Thanks for all your help. You have solved our problem. It's going to be a
headache to have to do this everytime we need to open a spreadsheet but it is
a solution.

"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?

Yes you can tell that to your friends... It would be true too - you worked it
out by working with me (just don't mention the second part :-)

Glad it worked out..

If you are familiar with macros then you can write one to do it everytime...

I can help you with that tomorrow if you need help.

"Plukey2" wrote:

Actually, I just need to change your &"E"& to &"E0"& and it works!!! Now
should I tell all my coworkers I figured this out? (Smile).. Just kidding...
Thanks for all your help. You have solved our problem. It's going to be a
headache to have to do this everytime we need to open a spreadsheet but it is
a solution.

"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?

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?

Ok, we got it over here finally! The below works 100% of the time.

=IF(ISNUMBER(A1),CONCATENATE(LEFT(FIXED(A1,0,TRUE) ,3),IF(LEN(FIXED(A1,0,TRUE))-3=10,"E","E0"),LEN(FIXED(A1,0,TRUE))-3),A1)

Ok, we can do the macro ok.

Thanks again for all your valued help!!!!!

"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?

Great. Thanks for the update.

"Plukey2" wrote:

Ok, we got it over here finally! The below works 100% of the time.

=IF(ISNUMBER(A1),CONCATENATE(LEFT(FIXED(A1,0,TRUE) ,3),IF(LEN(FIXED(A1,0,TRUE))-3=10,"E","E0"),LEN(FIXED(A1,0,TRUE))-3),A1)

Ok, we can do the macro ok.

Thanks again for all your valued help!!!!!

"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?