extract date from text
LOL... I was concentrating so on making sure I caught the 'st', 'nd' and
'rd' that I totally overlooked the mostly occurring 'th' suffix. Thanks for
catching/noting that.
For those still desiring to implement a formula of the form I originally
posted, here is the corrected formula...
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(M ID(
A1,FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st",""),"th" ,"")
--
Rick (MVP - Excel)
"Harlan Grove" wrote in message
...
"Rick Rothstein" wrote...
This formula will do what you want (providing there is always a space
after
the dash and there is no period after the year)...
=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(MID(A1,
FIND(" ",A1,FIND("-",A1)+2),LEN(A1))),"rd",""),"nd",""),"st","")
...
Your formula fails to delete the ordinal suffix "th", which,
inconveniently, appears in the OP's example data, so your formula
fails for the OP's sample data.
Probably best to use regular expressions to do this. For example,
using Laurent Longre's MOREFUNC.XLL add-in, you could use the formula
=--REGEX.SUBSTITUTE(A1,"^[^-]*-\s*\S+\s+(\d+)[^ 0-9]*\s+(\S+)\s+(\d+).*
$","[1]-[2]-[3]")
|