Need help in forcing number that wants to stay in Sci. Notation fo

[Phil]

Hello,

I have received an excel file from a client that is giving me fits. The
issue at hand is my inablility to convert a formatted column from number to
text, without it reverting back to scientific notation.

What is happening is that the column shows a numeric value, such as
17055201480000. When I try and paste that value in the adjacent column,
which I have pre-formatted as text, it changes to 1.70552E+13. And when I
look at the value in the edit/formula bar it shows as 17055201480000.

So I try it another way by creating a new column, leaving the formatting
alone (general by default), and then use copy/paste special/values it pastes
it as 17055201480000, and then when I go to change the format of the column
to text it reverts back to 1.70552E+13.

I know that there is a simple solution, but I am stumped. How do I do this?

Thanks in advance for your replies.

Phil

 

[Dave F]

Apply a custom format of 000000000000000 (that's 15 zeroes) to the cell that
has the number code then copy and paste anywhere else and retain the string
of numbers.

Magic!

Dave

 

[David Biddulph]

Have you tried formatting as number with zero decimal places?

 

[Bernd]

Hi,

Does

=TEXT(A1,"0")

help?

Regards,
Bernd

 

[Phil]

Dave,

I apologize if I wasn't clear in my original post, but the end result needs
to be text format, not numeric.

I DID try your suggestion, and then when I copied the value from the column
with the 000000000000000 format to the new column formatted as text (using
paste special/values), it still placed the value as Sci. Notation.

Did I do something incorrectly?

 

[Phil]

David,

I may have not been clear enough in my original post, but the end result is
that the number must be in TEXT format, not numeric, so setting the decimal
places to 0 would not benefit my situation.

 

[Phil]

Bernd,

That worked BEAUTIFULLY!

Thank You.