formatting column as text.

E

ellen

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.
 
D

Dave Peterson

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?
 
E

ellen

MAR06 is March, 2006. Excel doesn't want to format the columns to text. I
am using the format->cells->text option.
 
E

ellen

Yes. But maybe you're asking because you can't highlight the whole column?
do I have to highlight cells?
 
E

ellen

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 said:
Yes. But maybe you're asking because you can't highlight the whole column?
do I have to highlight cells?
Click to expand...
 
D

David Biddulph

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.
 
Top