Help Formating Numbers to Text

  • Thread starter Thread starter klafert
  • Start date Start date
K

klafert

I have a spreadsheet that contains the following data:

Date Account ID
5/14/08 5280-3-01
5/14/08 5280-3-01
5/14/08 5280-3-01
5/14/08 5280-3-01
5/14/08 5280-3-01
5/14/08 5280-3-01
5/14/08 1030-0-01
5/14/08 1030-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01
5/14/08 2101-0-01


When I save it as a .csv file it looks like this:

Date Account ID
5/14/2008 3/1/5280
5/14/2008 3/1/5280
5/14/2008 3/1/5280
5/14/2008 3/1/5280
5/14/2008 3/1/5280
5/14/2008 3/1/5280
5/14/2008 1030-0-01
5/14/2008 1030-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01
5/14/2008 2101-0-01


I have formatted it to Text, Number, and General etc. I have Copy the data
into a new spreadsheet, cleared all formatting. Tried this on two different
computers. No matter what it keeps changing the 2nd column to a data format.
Even though it doesn’t do it to the other account ID. I have imported this
stuff before and no problem. I am not importing into Excel.
 
Dave Peterson pointed out the problem earlier, Excel will convert text that
looks like a date into a date number. As Dave pointed out the text in say
Notepad will look like the following:

05/14/08,5280-3-01
05/14/08,5280-3-01
05/14/08,5280-3-01
05/14/08,5280-3-01
05/14/08,5280-3-01
05/14/08,5280-3-01
05/14/08,1030-0-01
05/14/08,1030-0-01
05/14/08,2101-0-01
05/14/08,2101-0-01


the trick is to open the file in Notepad and copy the data into Excel. The
data will be entered in one column.

Select the data
Choose Data, Text to Columns
Choose the Delimited Option
Click the Comma check box
Click Next
Select the Account Number column (click the column Heading)
In the Data Column Format click the Text radio button
Click the Finish button

Be careful that no data is overwritten.

HTH
Peter
 
Back
Top