How many times You just wanted to type some numbers diveded with slash, dot or hiven and Excel was converting into date automatically and You didn’t want that at all? In this article I will show You how to prevent formatting text as date by Excel.
Todays article includes small topic. However it was so annoying to me, that I decide to devote whole article for this. I had this issue building the same tool as in one of the previous articles and it took me a while to resolve this little thing.
Back to the point
So, lets start with the example key value, which Excel is converting automatically – 10/3.
Worksheets("Sheet1").Range("A1").Value = "10/3"Instead of 10/3 I got 3-Oct visible. The value inside cell was 10/3/2020. I was not expecting this at all.
I tried also with hiven 10-3 and dot 10.3, but with the same results.
I didn’t think that it is any issue and thought that apostrophe at the beginning of the value will help.
Worksheets("Sheet1").Range("A1").Value = "'10/3"For some reason it did not change the situation. Maybe it was caused by that it was workbook of the PowerPoint chart.
Anyway, I had to find other way to solve this problem.
After research I found the solution, which is based on firstly the format of the value. So before You put any value into the cell change the number format to string – @.
Worksheets("Sheet1").Range("A1").NumberFormat = "@"
Worksheets("Sheet1").Range("A1").Value = "10/3"Quick happy end
Excel is behaving how it wants and in its own way. When You want it to guess, it does not and when You don’t want it to guess, Excel is converting everything automatically on its own.
Now You should know how to prevent Excel from formatting text as date. It is not a hard topic. I can say it is small issue, but it can be a pain in the … when deadline of the project is near and You have to struggle with such things.
