Excel converts cell address to date

Question

I am saving cell addresses in a sheet but Excel converts some cells addresses to date like OCT8 to Oct-08, APR8 to apr-08.

Later on when getting cell values it gives date not cell addresses stored in the cell. Cell addresses are stored in an array and copying array by the following VBA code. It removes text formatting from cells and converts to custom date format:

.Range("c1").Resize(UBound(SA) + 1, 1).Value = Application.Transpose(SA)

What is the custom date format you are using? And are you only interested in the cell showing the date like "OCT8"? I'm a bit unclear as to what you what to achieve? A cell address is normally in the form of $A$1. — glh
I am saving cell adresses like "APR8, Oct8," but excel converts it to date. How to avoid automaticaly conversion to date. Cells format is text but still excel convert it to date — Mehmood Hassan
if you enter the data as 'APR8 and 'Oct8 this will override the auto conversion. — glh
Also what version of excel are you in because I've just tried Changing the NumberFormat to "TEXT" then entering "APR8" and it didn't change. Are you changing the number format before or after and is this via VBA or manual? — glh
Cell format is Text but after adding cell addressess through following vba line .Range("c1").Resize(UBound(SA) + 1, 1).Value = Application.Transpose(SA) it cell addtess OCT8 and APR12 to date. — Mehmood Hassan

glh glh · Accepted Answer · 2013-05-07T21:58:34

If you enter the data as 'APR8 and 'Oct8 this will override the auto conversion.

I've just tried changing the NumberFormat to "TEXT" then entering "APR8" and it didn't change. Are you changing the number format before or after? If you change the format before using range(..).numberformat = "@" this will keep it as text. Then paste you values.

Excel converts cell address to date

1 Answers