Cell format not changing

Copper Contributor

I am trying to clean all the cells in a column to have the Date format, specifically March 14, 2012, as previewed in Format options. In the Column D rows not shown, they have right alignment, with spaces between words and numbers, but not leading spaces. I did try to trim all the leading spaces from the values in this column, but it looks like it has no spaces between words either.

As you can see in my screenshot, the cells in column D are supposedly in Date format, but column K shows the results of =ISTEXT(D#), so for some reason Excel is still recognizing them as Text format. Screenshot 2024-04-03 172609.jpgWhen I try to trim column D values, all the values (both the ones I show, and the ones showing FALSE in column K) yield the date serial number, so column D is still formatting as Text?

1 Reply


Step 1:

Select column D.

Replace comma with comma followed by a space.


Step 2:

Press Alt+F11 to activate the Visual Basic Editor.

Press Ctrl+G to activate the Immediate window.

Type or copy/paste the following line:


Selection.Value = Selection.Value


With the insertion point in that line, press Enter.

Switch back to Excel.