How do I stop auto-correct to dates?

[captainentropy]

I'm using Excel 2007 and I have to use the text-to-columns feature a lot. I receive lots of text files from a collaborator with many columns of data. Many of these I prefer to sepearate even further to make their analysis easier. Many of the words in the strings that I'm separating are interpreted as dates but I don't want them corrected to dates. (e.g. MARCH5, which is NOT referring to a date, auto corrects to 5-Mar). I need to stop this - permanently. I can't figure out a way. Formatting the cells as text first doesn't work because when I use Text to Columns it replaces the text format of the new cells with "general", which auto-corrects. There is nothing in the auto-correct options that lets me disable this. Also, in the Text to Columns wizard, the last step where I select the data and column formats, I can only select the format for the first two columns that results from the delimiting. I can do Text to Columns for every column but sometimes I have up to 15 column to work with and with the number of files I have to deal with this is a burden.

I can't find a solution anywhere. Any suggestions?

 

[captainentropy]

OK, I figured out a workaround. It's a PITA though. I find and replace the separators that are always before the "words" that get auto corrected with ;' (any two symbols would work I'm sure). The T2C wizard will let me choose the separator. After that I format all the new cells as text then search for ' (apostrophe) then replace it with a space. I get the columns I need with the right data and not the auto-correct.

Regardless, there needs to be an option that globally turns of this auto-correct. It may be needed for some but not all. How would I contact MS to request this in future releases?

 

[Dryver14]

if the column where this is always being imported to is always the same ie no dates will need converting in the column you coul format as text and it will not change it then

 

[captainentropy]

I’ve tried that. Doesn’t work, and I have no idea why because you’ve said what other forums have also suggested.


Here’s an example of what I’m doing. I open the text file, containing the following, with Excel (there’s really dozens or hundreds of rows like this per file):

Window:[(103.25,103.499999,9), (103.3,103.549999,9), (103.35,103.599999,9), (103.4,103.649999,9), (103.45,103.699999,9)]:4.673772480145999E-9 [B]TAB[/B] 9.21E-14 [B]TAB[/B] (TOPBP1:103.207656,103.252757,9),(CDV3:103.255431,103.26811,9),(BFSP2:103.327253,103.382658,9),(TMEM108:103.386768,103.66413,9),

Now I have three columns of data since it automatically separates by tabs (had to spell them out since HTML doesn't support tabs). I need to separate “4.673772480145999E-9” into a new column and the “words” (these are gene names) inside the parenthesis on the right hand side into their own column too. So the first thing I do is replace “:[“ with “=” and “),(“ with ”;’” to get this:

Window=(103.25,103.499999,9), (103.3,103.549999,9), (103.35,103.599999,9), (103.4,103.649999,9), (103.45,103.699999,9)]:4.673772480145999E-9 9.21E-14 (TOPBP1:103.207656,103.252757,9;’CDV3:103.255431,103.26811,9;’BFSP2:103.327253,103.382658,9;’TMEM108:103.386768,103.66413,9),

I can then insert a column before the column containing the number “9.21E-14” and then use the T2C wizard on column 1 and separate at the colon, and then use the T2C wizard on the next column using the semicolon and colon as separators to get this:

Window=(103.25,103.499999,9), (103.3,103.549999,9), (103.35,103.599999,9), (103.4,103.649999,9), (103.45,103.699999,9)] 4.673772480145999E-9 9.21E-14 (TOPBP1 103.207656,103.252757,9 ’CDV3 103.255431,103.26811,9 ’BFSP2 103.327253,103.382658,9 ’TMEM108 103.386768,103.66413,9),

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I can then delete the superfluous columns (containing the numbers to the right of the gene name) and I can delete the extra parenthesis and the apostrophe. It’s a lot of work to get to this point but there is no other way. Now there is some kind of bug in the T2C wizard in step three where you select the format for the new columns in the preview window. Most of the time this does not work in that it only gives me the ability to designate the format of “text” (the option below the default of “general”) for the first two new columns. All new columns to the right of the first two do not get this option. Now, if what you suggested was true then after finishing the wizard I should be able to format all the columns as text and then delete the extra parenthesis and the apostrophe (which is now preceding every gene name) using find and replace without names such as MARCH5 auto-correcting to 5-Mar – but I can’t. It will always auto-correct to 5-Mar. What I have to do is in the F&R wizard is expand the "options" and select “text” as the format for the replaced cell. BUT, there is another bug here. If I have ‘MARCH5 in a cell(after the previous F&R) and find ‘ and replace it with nothing using text as the new format it won’t work (i.e. it doesn’t remove the apostrophe though it says it did). I have to put a space in the replace field for this to work which puts a space in front of the gene name. This doesn’t cause any downstream problems but it still is a bug and requires more work on my part.

I hope this makes sense. It’s a very convoluted workflow from beginning to end, I know, but this is the only solution I can find that works and stops this infuriating auto-correct.
<o></o>
There is no good reason the auto-correct to dates shouldn’t be able to be completely disabled.

 

[Dryver14]

so are each of these numbers in a column.
can you just show me 1 number how it
starts and what you want it to look like at the end.