Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ron Swinehart
 
Posts: n/a
Default 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?
  #2   Report Post  
Myrna Larson
 
Posts: n/a
Default

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?


  #3   Report Post  
Ron Swinehart
 
Posts: n/a
Default

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?



  #4   Report Post  
Myrna Larson
 
Posts: n/a
Default

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?




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
zero supress leading zeros when chg format from text to number HeatherO Excel Worksheet Functions 4 February 28th 05 12:11 AM
Number is in a text format dbl Excel Worksheet Functions 1 February 19th 05 09:57 PM
How do i change numbers in text format to number format? Greg New Users to Excel 1 December 14th 04 06:22 PM
Import Indian Rupees as a number format in Excel Aditya Khandekar Excel Discussion (Misc queries) 2 December 6th 04 04:48 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 07:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"