Excel dates have leading space in front of it…

Question

I have excel that is populated from database (I have no access to DB, just rendered excel). In Excel there are dates that I want to manipulate, my formulas kept failing until I realised that dates has leading space in front of dates (just note: space is not even visible, when you move your cursor left-right you won't notice... only when you hit backspace and enter it is converted to real date...).

I tried adding extra column and formula: =IF(ISTEXT($A2);TRIM($A2);$A2) it is not helping... Only thing that works is you have to enter in each date, move cursor at the beginning in formula bar, hit "backspace" hit "enter" and only then is converted to real date.

here is my screenshoot:

question from:https://stackoverflow.com/questions/65902377/excel-dates-have-leading-space-in-front-of-it

Answer 1

Select column than Data > Text to Columns Next, Next, then select Date 'DMY'.... did the trick.