Date Formatting

**PSFM_Richard** · 10-18-2007, 02:57 PM

I have received a spreadsheet at work and the date column has been filled in as text in the format "4th November 2007" for over 400 entries.

Is their any way to convert this to a date format "DD/MM/YYYY"?

Many thanks for your help in advance.

**Leith Ross** · 10-18-2007, 03:14 PM

Hello PSFM_Richard,

What does the text in the Formula Bar look like when you select one these cells?

Thanks,
Leith Ross

**VBA Noob** · 10-18-2007, 03:15 PM

Try

Select Column > Press ctrl + H > Find enter th and replace leave blank

VBA Noob

**daddylonglegs** · 10-18-2007, 03:48 PM

.....presumably you might also need to do a Find/Replace on "nd", "rd" and "st"

**VBA Noob** · 10-18-2007, 03:49 PM

Unless you do Find ust and replace with blank

VBA Noob

**daddylonglegs** · 10-18-2007, 03:53 PM

Sorry VBA Noob,

I edited my post because when I re-tested I noticed that replacing "st" in 1st August 2007 gave 1 Augu 2007 and that still succesfully converted to a date [to my surprise]