How do I get it to stop converting to dates?

snipescc

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

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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,650

ADVERTISEMENT

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

snipescc

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

ADVERTISEMENT

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
129
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,650
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top