| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with Formatting Number as Text
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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formatting Number as Text
Insert ' before your account ids.
"klafert" wrote: 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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formatting Number as Text
I forgot to mention that the data must be pasted using the Paste Special
method and pasted as text. This is most important! The data will be formatted if you do not do this. "klafert" wrote: 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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formatting Number as Text
I have done that didn't work. Woks in the .xls file but not the .csv file.
"Billy Liddel" wrote: I forgot to mention that the data must be pasted using the Paste Special method and pasted as text. This is most important! The data will be formatted if you do not do this. "klafert" wrote: 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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formatting Number as Text
I will try I know that works in the .csv file but I don't think I can import
using tha character. "Sheeloo" wrote: Insert ' before your account ids. "klafert" wrote: 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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help with Formatting Number as Text
I have had this issue many times with the stuff that I import and there are a
couple different way one way that I find wonderful is this macro, It changes all of those weird hidden characters so that excel reads it as a number and not text. Dim r As Range Dim Count As Long Count = 0 For Each r In ActiveSheet.UsedRange If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub Another way is to do the text to column, as you import you can choose the format. "klafert" wrote: I have done that didn't work. Woks in the .xls file but not the .csv file. "Billy Liddel" wrote: I forgot to mention that the data must be pasted using the Paste Special method and pasted as text. This is most important! The data will be formatted if you do not do this. "klafert" wrote: 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 doesnt do it to the other account ID. I have imported this stuff before and no problem. I am not importing into Excel. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formatting a number as accounting in a text reference. | Excel Discussion (Misc queries) | |||
| Formatting from Number to Text | Excel Discussion (Misc queries) | |||
| formatting number value to text value in excel | Excel Worksheet Functions | |||
| Text Function with Different Formatting for Number | Excel Discussion (Misc queries) | |||
| Formatting a cell as "text" in the number catagory. | Excel Worksheet Functions | |||
Linear Mode
