Extract Date

[knot]

Hi all!

I am looking to extract the date from the following text:

GB / Redc 8th Sep / 14:20 7f Nursery

All i need is the 8th part. I can do this manually using the Excel Text To Columns feature but occasionally this does not work correctly. For example if i use the Text To Columns feature to extract the 8th from the following text for some reason i also get the Hcap Chs parts extracted.

GB / Worc 8th Sep / 14:10 2m4f Nov Hcap Chs
GB / Worc 8th Sep / 14:10 2m4f Nov Hcap Chs


So what i require is a reliable formula that will extract the 8th from all of the above race texts.

Any ideas please?

[Captain Sensible]

Are you looking for a formula or VBA?

Here's a formula to pick out the 4th word in a text string in cell A1


=MID(MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",3),1,256),FIND("^",SUBSTITUTE(A1," ","^",3)),256))-2)

If it's a regular format it might be better for you to write a small VBA function using split in VBA rather than using long formulas

[knot]

Cheers Captain, nice one!