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

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Phil" wrote:

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

Have you tried formatting as number with zero decimal places?
--
David Biddulph

"Phil" wrote in message
...
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

Hi,

Does

=TEXT(A1,"0")

help?

Regards,
Bernd

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?

"Dave F" wrote:

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
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Phil" wrote:

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

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.


"David Biddulph" wrote:

Have you tried formatting as number with zero decimal places?
--
David Biddulph

"Phil" wrote in message
...
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

Bernd,

That worked BEAUTIFULLY!

Thank You.

"Bernd" wrote:

Hi,

Does

=TEXT(A1,"0")

help?

Regards,
Bernd