How do I get it to stop converting to dates?

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Legend
Joined
Jun 19, 2002
Messages
63,651
If you format the cells as Text before using Text to columns they shouldn't get converted to dates.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Change the Column Data Format to Text for the first column.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Sorry, I meant the column to the right that receives the string containing -
 

snipescc

Board Regular
Joined
Aug 29, 2010
Messages
125
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
I have little doubt that I'm missing something, but I tried with the data you showed.
Excel Workbook
ABCD
1
201-01 D Smith John
301-01 D Smith John
401-01 D Smith John
501-01D SmithJohn
601-01D SmithJohn
701-01D SmithJohn
801-01DSmithJohn
901-01DSmithJohn
1001-01DSmithJohn
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

Board Regular
Joined
Aug 29, 2010
Messages
125
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

Legend
Joined
Jun 19, 2002
Messages
63,651
Result in 2010 using GTO's method

Excel Workbook
ABC
101-01D SmithJohn
201-01D SmithJohn
301-01D SmithJohn
401-01D SmithJohn
501-01D SmithJohn
601-01D SmithJohn
Sheet2
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 
Last edited:

Forum statistics

Threads
1,085,715
Messages
5,385,419
Members
401,945
Latest member
Paul82

Some videos you may like

This Week's Hot Topics

Top