How do I get it to stop converting to dates?

snipescc

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

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
If you format the cells as Text before using Text to columns they shouldn't get converted to dates.
 
Upvote 0
Sorry, I meant the column to the right that receives the string containing -
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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