Format column to date

[RHONDAK72]

Is there an easy way to write in some VBA code that will first remove any and all characters from a column that would prevent it from changing the format to date format mm/dd/yyyy?

For example, some cells in the column might contain a space, a special character or even a text string for a note that the user typed next to the date within the same cell. Is there a way to quickly remove any and all characters so that the cells in the column can be formatted into a date mm/dd/yyyy format? Thanks.

 

[mamady]

Hi Dear,

This can be done using formulas, over two steps:
1- extract the numbers only
2- convert the extracted numbers to date format

so, assuming that your input data in cell A1, then use the following formula
=SUMPRODUCT(MID(0&A1, LARGE(INDEX(ISNUMBER(--MID(A1, ROW(INDIRECT("1:"&LEN(A1))), 1)) * ROW(INDIRECT("1:"&LEN(A1))), 0), ROW(INDIRECT("1:"&LEN(A1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(A1)))/10)

then, use the following formula to convert to date (assuming the extracted numbers are in cell B1)
=DATE(RIGHT(B1,4),LEFT(B1,2),MID(B1,3,2))

Best Regards
M. Yusuf