Parsing Text and Converting to Date?

[EatingSteak]

I'm trying to take a rather clumsy spreadsheet (sent to me periodically by a customer) and distill the crap into usable parts.

So I have a text-input cell with the 'date' in it, which appears like "Oct. 1 - 7, 2000" (see screenshot). Naturally this is for the week of Oct 1st, 2000.

Can I 'translate' this into usable numbers? It would be nice to create a new column to do this.

I see Excel has a "Text to Columns" Wizard, but I'm having a hard time coming up with a way to do it through that. The months "June" and "July" are spelled out (there's no period), and "May" only has three letters, where others have four, or three plus a dot.

Any way to do this systematically? Or am I just better off processing the spreadsheets manually?

 

[Joe4]

So I have a text-input cell with the 'date' in it, which appears like "Oct. 1 - 7, 2000" (see screenshot). Naturally this is for the week of Oct 1st, 2000.

Can I 'translate' this into usable numbers? It would be nice to create a new column to do this.

So what exactly do you want to get out of this?
Are you just looking for the first date in the range (i.e. Oct. 1, 2000 in your example)?

 

[Joe4]

If you are just looking for the first date in the date range in a valid date format, use a formula like this (for a value in cell A2):

=DATEVALUE(SUBSTITUTE(LEFT(A2,FIND("-",A2)-1),".","") & MID(A2,FIND(",",A2),6))

 

[EatingSteak]

Wow - totally nailed it. Thanks. That was exactly what I was trying to do.

 

[Joe4]

You are welcome.
Yes, I have had lots of experience in cleaning up poorly formatted data too!