Format number as text on import
 

When importing or pasting a number with a leading zero, (05003230) Excel
automatically formats it as a number and loses the leading zero (5003230). I
do not want to have to manually format the appropriate cells prior to the
import, since I do not know where they will be located.

I tried to add a single-quote character as the first character of the
exported number ('05003230), hoping Excel would format that cell as text,
but instead Excel left the quote as part of the data in the cell.

Is there any way to tell Excel not to drop leading zeros in these cells?

If these cells are all in the same column(s), and it's a text file (extension
txt, not csv), so the Import Wizard comes up when you open the file, you can
specify that this column is to be treated as text.

BUT... how did you export? Did you look at the file with Notepad to be sure
the leading 0's were exported?

On Fri, 4 Mar 2005 07:19:05 -0800, "Ron Swinehart" <Ron
wrote:

When importing or pasting a number with a leading zero, (05003230) Excel
automatically formats it as a number and loses the leading zero (5003230). I
do not want to have to manually format the appropriate cells prior to the
import, since I do not know where they will be located.

I tried to add a single-quote character as the first character of the
exported number ('05003230), hoping Excel would format that cell as text,
but instead Excel left the quote as part of the data in the cell.

Is there any way to tell Excel not to drop leading zeros in these cells?

Thanks for responding.

These values came from the Results Pane of SQL Query Analyzer. I set the
output format to tab delimited. Usually I copy the entire results pane to
the clipboard and paste it into an empty worksheet in Excel. I can also save
it to a file, but that is an extra step.

This output consists of the result sets of several queries, so the affected
values are in some of the rows of several columns. I suppose I could save to
a file and import, changing all the columns to text, but that would take a
lot of work each time I import. Some of the result sets have 80 columns.

I can see in the source that the leading zeros exist before importing into
Excel. I have encountered this problem before. I am surprised Microsoft
does not have a better solution for it.

"Myrna Larson" wrote:

If these cells are all in the same column(s), and it's a text file (extension
txt, not csv), so the Import Wizard comes up when you open the file, you can
specify that this column is to be treated as text.

BUT... how did you export? Did you look at the file with Notepad to be sure
the leading 0's were exported?

On Fri, 4 Mar 2005 07:19:05 -0800, "Ron Swinehart" <Ron
wrote:

When importing or pasting a number with a leading zero, (05003230) Excel
automatically formats it as a number and loses the leading zero (5003230). I
do not want to have to manually format the appropriate cells prior to the
import, since I do not know where they will be located.

I tried to add a single-quote character as the first character of the
exported number ('05003230), hoping Excel would format that cell as text,
but instead Excel left the quote as part of the data in the cell.

Is there any way to tell Excel not to drop leading zeros in these cells?

You can try preformatting the relevant columns as text before pasting the
data, or you can set a custom format such as 00000 for these columns to
display the leading 0's.

On Fri, 4 Mar 2005 13:51:01 -0800, "Ron Swinehart"
wrote:

Thanks for responding.

These values came from the Results Pane of SQL Query Analyzer. I set the
output format to tab delimited. Usually I copy the entire results pane to
the clipboard and paste it into an empty worksheet in Excel. I can also save
it to a file, but that is an extra step.

This output consists of the result sets of several queries, so the affected
values are in some of the rows of several columns. I suppose I could save to
a file and import, changing all the columns to text, but that would take a
lot of work each time I import. Some of the result sets have 80 columns.

I can see in the source that the leading zeros exist before importing into
Excel. I have encountered this problem before. I am surprised Microsoft
does not have a better solution for it.

"Myrna Larson" wrote:

If these cells are all in the same column(s), and it's a text file
(extension
txt, not csv), so the Import Wizard comes up when you open the file, you
can
specify that this column is to be treated as text.

BUT... how did you export? Did you look at the file with Notepad to be sure
the leading 0's were exported?

On Fri, 4 Mar 2005 07:19:05 -0800, "Ron Swinehart" <Ron
wrote:

When importing or pasting a number with a leading zero, (05003230) Excel
automatically formats it as a number and loses the leading zero (5003230).
I
do not want to have to manually format the appropriate cells prior to the
import, since I do not know where they will be located.

I tried to add a single-quote character as the first character of the
exported number ('05003230), hoping Excel would format that cell as text,
but instead Excel left the quote as part of the data in the cell.

Is there any way to tell Excel not to drop leading zeros in these cells?