| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formatting column as text.
i have data in a column that looks like this - mar06-may07. i want to use
"text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
How about telling excel that both of the columns should be Text when you're
going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
MAR06 is March, 2006. Excel doesn't want to format the columns to text. I
am using the format-cells-text option. "Dave Peterson" wrote: How about telling excel that both of the columns should be Text when you're going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
Did you specify Text in Step 3 of that dialog for each field?
ellen wrote: MAR06 is March, 2006. Excel doesn't want to format the columns to text. I am using the format-cells-text option. "Dave Peterson" wrote: How about telling excel that both of the columns should be Text when you're going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
Yes. But maybe you're asking because you can't highlight the whole column?
do I have to highlight cells? "Dave Peterson" wrote: Did you specify Text in Step 3 of that dialog for each field? ellen wrote: MAR06 is March, 2006. Excel doesn't want to format the columns to text. I am using the format-cells-text option. "Dave Peterson" wrote: How about telling excel that both of the columns should be Text when you're going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson -- Dave Peterson |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
Never mind. it didn't work. I highlighted the whole column, then did
format-cells-text. then I did text-to-column on that same data set using a space as the delimiter (I removed the '-'), and it converted the destination cell contents to 3-may. the origin cell remained mar06 (which is correct). I think the problem is that when the destination column is empty to begin with, it doesn't have anything to format, so it must be defaulting to number. unfortunately, you can't let the data fill in (with the wrong format) and then try to reformat. The cell actually has 05/03/2007 now. "ellen" wrote: Yes. But maybe you're asking because you can't highlight the whole column? do I have to highlight cells? "Dave Peterson" wrote: Did you specify Text in Step 3 of that dialog for each field? ellen wrote: MAR06 is March, 2006. Excel doesn't want to format the columns to text. I am using the format-cells-text option. "Dave Peterson" wrote: How about telling excel that both of the columns should be Text when you're going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formatting column as text.
The formatting is done towards the end of the Data/ Text to Columns process.
At step 3 of 3 in the Wizard, select the header of each column in turn and set the format to Text. You need to do that *before* you hit the "Finish" button of the Wizard. -- David Biddulph "ellen" wrote in message ... Never mind. it didn't work. I highlighted the whole column, then did format-cells-text. then I did text-to-column on that same data set using a space as the delimiter (I removed the '-'), and it converted the destination cell contents to 3-may. the origin cell remained mar06 (which is correct). I think the problem is that when the destination column is empty to begin with, it doesn't have anything to format, so it must be defaulting to number. unfortunately, you can't let the data fill in (with the wrong format) and then try to reformat. The cell actually has 05/03/2007 now. "ellen" wrote: Yes. But maybe you're asking because you can't highlight the whole column? do I have to highlight cells? "Dave Peterson" wrote: Did you specify Text in Step 3 of that dialog for each field? ellen wrote: MAR06 is March, 2006. Excel doesn't want to format the columns to text. I am using the format-cells-text option. "Dave Peterson" wrote: How about telling excel that both of the columns should be Text when you're going through that data|text to columns dialog. (Don't use General) If you want to change the strings to real dates later, you'll want to post back. Is Mar06=March 1, 2006 or March 6, 2007? ellen wrote: i have data in a column that looks like this - mar06-may07. i want to use "text to column' and separate the two dates. i have formatted both columns as text so it won't 'interpret' Mar06 as 3-Mar or may07 as 7-may. BUT it won't stop formatting the data as 6-Mar or 7-may. -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Text String: Formatting Text and Numbers? | Excel Discussion (Misc queries) | |||
| Conditional Formatting based on text within a cell w/ text AND num | Excel Worksheet Functions | |||
| Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
| column to column conditional formatting won't work, need formula | Excel Worksheet Functions | |||
| Conditional Formatting based on Text within Text | Excel Discussion (Misc queries) | |||
Linear Mode
