I have an excel file that has one column for Product No (alphanumeric). I
have found that some of the cell are displaying Scientific Notation (e.g.
4.00000e+016) even when I specified the formatting to be text. The only way
I could get them to show as 40000001235 is to go to each cell and click on
the formula bar. Is there an easier way? I've tried =Value(abc123),
=abc123, copy and paste special/values...but none of these worked. To top it
off, the formula remains instead of showing the value: =abc123 instead of
4.0000e+016.

Format the cell as text BEFORE entering the text. Or put and asterisk ' at
the beginning of the entry. HTH Otto
"FredL" wrote in message
...
I have an excel file that has one column for Product No (alphanumeric). I
have found that some of the cell are displaying Scientific Notation (e.g.
4.00000e+016) even when I specified the formatting to be text. The only
way
I could get them to show as 40000001235 is to go to each cell and click on
the formula bar. Is there an easier way? I've tried =Value(abc123),
=abc123, copy and paste special/values...but none of these worked. To top
it
off, the formula remains instead of showing the value: =abc123 instead of
4.0000e+016.

I've tried that where I copied and pasted to a new workbook that was
previously formatted as text. It didn't work. The original file is a
download from a system and has 7K lines; this isn't a manual entry.

"Otto Moehrbach" wrote:

Format the cell as text BEFORE entering the text. Or put and asterisk ' at
the beginning of the entry. HTH Otto
"FredL" wrote in message
...
I have an excel file that has one column for Product No (alphanumeric). I
have found that some of the cell are displaying Scientific Notation (e.g.
4.00000e+016) even when I specified the formatting to be text. The only
way
I could get them to show as 40000001235 is to go to each cell and click on
the formula bar. Is there an easier way? I've tried =Value(abc123),
=abc123, copy and paste special/values...but none of these worked. To top
it
off, the formula remains instead of showing the value: =abc123 instead of
4.0000e+016.

Hi Fredl

try

=text(a2,"###")
copy and paste special values

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"FredL" escreveu:

I've tried that where I copied and pasted to a new workbook that was
previously formatted as text. It didn't work. The original file is a
download from a system and has 7K lines; this isn't a manual entry.

"Otto Moehrbach" wrote:

Format the cell as text BEFORE entering the text. Or put and asterisk ' at
the beginning of the entry. HTH Otto
"FredL" wrote in message
...
I have an excel file that has one column for Product No (alphanumeric). I
have found that some of the cell are displaying Scientific Notation (e.g.
4.00000e+016) even when I specified the formatting to be text. The only
way
I could get them to show as 40000001235 is to go to each cell and click on
the formula bar. Is there an easier way? I've tried =Value(abc123),
=abc123, copy and paste special/values...but none of these worked. To top
it
off, the formula remains instead of showing the value: =abc123 instead of
4.0000e+016.

Wow! 10 million thanks for that little trick. I appreciate it. I have been
stuck on that one for a while.

"Marcelo" wrote:

Hi Fredl

try

=text(a2,"###")
copy and paste special values

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"FredL" escreveu:

I've tried that where I copied and pasted to a new workbook that was
previously formatted as text. It didn't work. The original file is a
download from a system and has 7K lines; this isn't a manual entry.

"Otto Moehrbach" wrote:

Format the cell as text BEFORE entering the text. Or put and asterisk ' at
the beginning of the entry. HTH Otto
"FredL" wrote in message
...
I have an excel file that has one column for Product No (alphanumeric). I
have found that some of the cell are displaying Scientific Notation (e.g.
4.00000e+016) even when I specified the formatting to be text. The only
way
I could get them to show as 40000001235 is to go to each cell and click on
the formula bar. Is there an easier way? I've tried =Value(abc123),
=abc123, copy and paste special/values...but none of these worked. To top
it
off, the formula remains instead of showing the value: =abc123 instead of
4.0000e+016.