Converting text Date to Excel date

I have a large spreadsheet with column that contain dates in text format, but with a space at each end of the string. If I edit each cell to remove the spaces, the string converts to an Excel date. Is there a formula I can use in a looping macro to eliminate those spaces down the entire column?

Richard: Thanks, but using Find[space] Replace[null] results in an error message saying Excel cannot find a match. And the Find & Replace box wants to work on the whole sheet, not just one column or range.

Originally Posted by Richard Buttrey

Hi,

Rather than a macro why not just select the whole range of text dates and use Find [space] Replace [null]

If Find/Replace is running on the entire sheet then you haven't selected a range, row or column.

Does it matter if they are converted to dates? You can just add a number format to the column so they display however you want.

Also, if you want to use a formula you don't then need a macro, just ensure that any cell references are relative then copy it down against all rows - you could try something like =MID(A1,2,LEN(A1)-2)

Thank you very much. That was not a function I'd used before, and it worked quite well.

I did, but the next reply solved the problem. Thanks for your help.

Hello vzw1965,

first you have to append " ' " apostasy of each cell of date by making below formula:
consider your Textdate column is A then write as formula in column B

Please Login or Register  to view this content.

then make value of column B
Edit a cell and copy last space then select entire column of B and replace that copied text to blank

Hi Sir,
i read the #1 post there is no concerning the leading space.

his post is:
I have a large spreadsheet with column that contain dates in text format, but with a space at each end of the string. If I edit each cell to remove the spaces, the string converts to an Excel date. Is there a formula I can use in a looping macro to eliminate those spaces down the entire column?