How do I get it to stop converting to dates?

[snipescc]

Hi everyone. I've been having a problem lately. Excel loves to convert any number with a dash or slash in it into a date, and sometimes I really don't want it to. I'm text-to-columning a list of people who are assigned divisions in the format 01-01, and as soon as I do text to columns, it converts their division to a data, which I really don't want. The original cells look like this.
<table style="width: 352px; height: 36px;" border="0" cellpadding="0" cellspacing="0"><col width="291"><tr height="20"> <td style="height: 15pt; width: 218pt;" width="291" height="20"> 01-01 D Smith John</td> </tr></table>In the past I have tried every cell format to get it to stop converting numbers that have a dash or slash in it to a date, and nothing has worked except putting a single quote mark in first. But I'd like to know if there is another way to get it to stop turning my divisions into dates.

 

[VoG]

If you format the cells as Text before using Text to columns they shouldn't get converted to dates.

 

[GTO]

Change the Column Data Format to Text for the first column.

 

[snipescc]

Unfortunately, it still did. I think Excel has a thing for dates.

 

[VoG]

Sorry, I meant the column to the right that receives the string containing -

 

[snipescc]

I formatted every one of the first 5 columns as text, it still converts them. I really appreciate the help, this just isn't working.

I worked out a bit of a hack, I concatenated a single quote before everything and then did text to column, but it's awkward and I'd like to find a better solution.

 

[GTO]

I have little doubt that I'm missing something, but I tried with the data you showed.

Excel Workbook
	A	B	C	D
1
2	01-01 D Smith John
3	01-01 D Smith John
4	01-01 D Smith John
5	01-01	D Smith	John
6	01-01	D Smith	John
7	01-01	D Smith	John
8	01-01	D	Smith	John
9	01-01	D	Smith	John
10	01-01	D	Smith	John
Sheet6

Excel 2003
Starting with the raw data shown in rows 2,3 and 4... in rows 5,6 and 7, I used Text 2 Columns, using Fixed width, ditched the second break (so D and Smith are together) and on the next step, changed the first column to Text in the Text to Column Dialog. In rows 8,9 and 10, I used delimiter (space) and did the same thing.

What version are you using (I am currently in 2003)

 

[snipescc]

I'm using 2010, which might be the issue. It might not be completely debugged yet. My general attitude with Microsoft products is that the best one is the last version, since it will have been debugged more then the newer ones.

 

[VoG]

Result in 2010 using GTO's method

Excel Workbook
	A	B	C
1	01-01	D Smith	John
2	01-01	D Smith	John
3	01-01	D Smith	John
4	01-01	D Smith	John
5	01-01	D Smith	John
6	01-01	D Smith	John
Sheet2

 

[GTO]

I am sorry I am not being of help. Just to make sure, in Step 3 of 3, you have changed the first column to Text and this does not "stick" (It should change the destination cells' number format to Text.)

Thanks Peter Hope everything is well at your end