Date formatting from text

[Guest]

I have a rather large spreadsheet where someone has input the date as text
eg. 12th January 2004, and 2nd April 2001.

Is there a simple way that I can get this into a numerical format ie
12/01/2004 so can easily use it to calculate time periods along with other
date I have?

Thanks!

 

[JE McGimpsey]

One way:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd",""),"th","")

Format as a date.

In-place:

Do three Edit/Replaces (i.e., replace st, nd and th with nothing).

 

[David Biddulph]

There may be simpler ways, but one option (to get rid of the "th" and "nd",
and thus convert to something Excel will read as a date) is
=--(LEFT(A1,FIND(" ",A1)-3)&RIGHT(A1,LEN(A1)-FIND(" ",A1)))

 

[Guest]

works a treat - thanks!

One way:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"st",""),"nd",""),"th","")

Format as a date.

In-place:

Do three Edit/Replaces (i.e., replace st, nd and th with nothing).

 

[JE McGimpsey]

Forgot to add "rd", but you probably caught it...

 

[Ron Rosenfeld]

I have a rather large spreadsheet where someone has input the date as text
eg. 12th January 2004, and 2nd April 2001.

Is there a simple way that I can get this into a numerical format ie
12/01/2004 so can easily use it to calculate time periods along with other
date I have?

Thanks!

You can use this UDF. If the target is already a proper date, it won't change
it, but if it isn't it will make the change. You will have to format the cell
with the function as a date, or else it will appear as a serial number (e.g. 12
jan 2004 = 37998)

If the contents cannot be converted into a date, you will get a VALUE error.

You can enter the formula as:

=reDate(cell_ref)

To enter the UDF, <alt-F11> opens the VBEditor.

Ensure your project is highlighted in the project explorer window, then
Insert/Module and paste the code below into the window that opens:

==========================================
Option Explicit
Function reDate(str) As Date
Dim re As Object
Dim ResultString As String
Set re = CreateObject("vbscript.regexp")
re.Global = True
re.Pattern = "(\d+)[A-Za-z]{2}"
ResultString = re.Replace(str, "$1")
reDate = DateValue(ResultString)
End Function
===============================
--ron