Extract Dates

[howard]

I have text below and am trying to extract the first date in B3 i.e 15 June 2015 and the second date in C3 i.e . 20 June 2015

It would be appreciated if someone could kindly assist me

Excel 2012
	A	B
3	David Martins - CT-BKT return 15 June 2015 - 20 June 2015	June 2015 - 20 June 2015
Sheet1

Cell Formulas
Range	Formula
B3	=RIGHT(A3,FIND("return",A3))

 

[Peter_SSs]

Cell C1 contains the formula

=DATEVALUE(TRIM(LEFT(B1,FIND(" - ",B1))))

Cell D1 contains the formula

=DATEVALUE(TRIM(MID(B1,FIND(" - ",B1)+2,555)))

Just a comment that the TRIM() function in these formulas is superfluous as the DATEVALUE() function will force that action anyway.

 

[hicksi]

Just a comment that the TRIM() function in these formulas is superfluous as the DATEVALUE() function will force that action anyway.

Thanks. I never thought it did work with multiple spaces.
BTW, I've been caught by char(160). Not entirely sure what gets rid of that.

To explain... Copying stuff from a web page or a word document, there are instances where hard-space or non-breaking-space ( ampersand-NBSP-semicolon ) comes across.
DATEVALUE hates that (and TRIM doesn't fix it either).
=DATEVALUE(A1) fails
=DATEVALUE(TRIM(A1)) fails
=DATEVALUE(SUBSTITUTE(A1,CHAR(160)," ")) works

 

[Peter_SSs]

BTW, I've been caught by char(160). Not entirely sure what gets rid of that.

Haven't you answered that below?

=DATEVALUE(SUBSTITUTE(A1,CHAR(160)," ")) works

 

[howard]

Once gain thanks for your help

I would like to amend your formula, so that if "Compassionate Leave" appears in Col D for eg D11, the formula below will return a blank

 =IF(D10="comp*","",DATEVALUE(TRIM(LEFT(D11,FIND(" - ",D11)))))

It now returns #value!

Excel 2012
	D	E
11	Compassionate Leave	#VALUE!
Sheet2

Cell Formulas
Range	Formula
D11	=IFERROR(IF(C11="*comp*","",TRIM(MID(A11,SEARCH("RETURN",A11)+6,555))),"Compassionate Leave")
E11	=IF(D10="comp*","",DATEVALUE(TRIM(LEFT(D11,FIND(" - ",D11)))))

 

[hicksi]

Haven't you answered that below?

That achieves a result. But are there better ways?
Are there functions that get rid of the specialised characters like hard-space, long-dash, the variants of apostrophe (and I'm sure there are others) that WORD puts into text (and which ends up on web pages when users save html from WORD)

 

[Peter_SSs]

That achieves a result. But are there better ways?
Are there functions that get rid of the specialised characters like hard-space, long-dash, the variants of apostrophe (and I'm sure there are others) that WORD puts into text (and which ends up on web pages when users save html from WORD)

CLEAN() will get rid of some problem characters however there is no native function that will handle all the ones you mentioned.

In any case it would be difficult as I suspect some of the characters that you are referring to you would want to delete, but others (like char(160)) you would probably want to substitute another character (eg a space). If you are repeatedly dealing with similar situations along these lines you could probably write a user-defined function to perform the actions you want & return the sanitised results into another column or an ordinary macro to clean up the data in-situ. However, if you wanted to investigate that further in the forum, you should start your own thread as I think we have hijacked this one about enough.