disable automatic text-to-date conversion in Excel

Question

disable automatic text-to-date conversion in Excel

Anonymous

Dear Microsoft support forum users,

How can I disable automatic text-to-date conversion in Excel? I am a biologist working with gene names such as "MARCH6", “SEPT6” etc, I do not like the auto conversion of these gene names to dates. I'd appreciate it if you could give me any inputs.

Thanks ahead,

Xiao

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

3 additional answers

Answer 1

OssieMac 48,001 Volunteer Moderator

Select the column then Select Number Format and set to Text and then anything typed in the cells should remain as text with out converting to a Date.

Alternatively insert a single quote then type the name and it will remain as text. The single quote can be seen in the formula Bar but is not visible in the cell.

0 comments

Answer 2

Hi, OssieMac,

Thanks for the reply. The single quote works very well. I tried to use right-click and set the format to "text", unfortunately, Excel changed my gene name to a number, for example, if my gene name is "MARCH11", in Excel it shows "11-Mar", after I change the format to text, in Excel it shows "43901", I expect it shows "MARCH11", somehow there are glitches of Excel that fail to do this. I am still confused why Excel does the date conversion automatically and this could not be disabled.

Best,

Xiao

Answer 3

Anonymous

Dear

You need to change the format before any editing, eg all column that will contain that sort of text data

Regards

JY

0 comments

Answer 4

Anonymous

Dear JY,

I see. Thanks a lot for your help.

Regards,

Xiao

0 comments

Share via

disable automatic text-to-date conversion in Excel

3 additional answers