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))

 

[tygrrboi]

=RIGHT(A3,LEN(A3)-FIND("return",A3)-6)

 

[Aladin Akyurek]

In B3 enter:

=TRIM(SUBSTITUTE(REPLACE(A3,1,SEARCH("return",A3)+5,""),2015,"",1))

 

[howard]

Thanks for the help. It is extracting 15 June 2015 - 20 June 2015 -see below

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

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

I have manually shown what result should in Col B & C below. Kindly amend your formula so as to compute the correct result

Excel 2012
	A	B	C
5	David Martins - CT-BKT return 15 June 2015 - 20 June 2015	15-Jun-15	20-Jun-15
Sheet1

 

[hicksi]

I have put the values in C and D

Cell B1 contains the formula

=TRIM(MID(A1,FIND("RETURN",UPPER(A1))+6,555))

Cell C1 contains the formula

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

Cell D1 contains the formula

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

... which means that C1 and D1 contain a number. Go format that as you please to make it look like a date. Or use the values. Your call.

Thanks for the help. It is extracting 15 June 2015 - 20 June 2015 -see below
Excel 2012

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

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

​

 

[Aladin Akyurek]

In B3 enter:

=TRIM(LEFT(D3,FIND("-",D3)-1))

In C3 enter:

=TRIM(REPLACE(D3,1,FIND("-",D3),""))

In D3 enter:

=TRIM(REPLACE(A3,1,SEARCH("return",A3)+5,""))

Note. Calling FIND to locate "return" is risky: SEARCH is the right function to call upon.

 

[bosco_yip]

Maybe……..

In B3, formula copy across to C3 :

=TRIM(MID(SUBSTITUTE(" - "&MID($A3,SEARCH("return",$A3)+7,99)," - ",REPT(" ",50)),COLUMNS($A3:A3)*50,50))

Regards

 

[sokgat]

Book1
	A	B	C
1	David Martins - CT-BKT return 15 June 2015 - 20 June 2015	15 June 2015	20 June 2015
Sheet1

Cell Formulas
Range	Formula
B1	=LEFT(RIGHT(A1,LEN(A1)-FIND("return",A1)-6),FIND("-",RIGHT(A1,LEN(A1)-FIND("return",A1)-6))-2)
C1	=RIGHT(RIGHT(A1,LEN(A1)-FIND("return",A1)-6),LEN(RIGHT(A1,LEN(A1)-FIND("return",A1)-6))-FIND("-",RIGHT(A1,LEN(A1)-FIND("return",A1)-6)))

Is this correct?

 

[howard]

Thanks Guys for all the help, much appreciated

 

[hicksi]

Note. Calling FIND to locate "return" is risky: SEARCH is the right function to call upon.

Almost agree. Personally, I am not a fan of anything where I cannot control wildcards. I'm having a really bad time with users entering
"*** STANDBYE" or something similar when they want the text to stand out. Those asterisks mess up LOOKUP bigtime.