Find and show the strings

[excel_1999]

Hi all,

I need to filter extra content in "column A" just show the Month and Year on "column B"
I try to use the stupid method to filter out the month first but not work.

B2 =MID(A2,FIND("jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec",A2)+0,3)

A B

Date Source	Date
Payment date: March 2019	Mar 19
Issue at may 2018 by peter	May 18
jan 2017 enter by Tom	Jan 17
T18603 contract 5 December 2018	Dec 18

<tbody>
</tbody>

Thx all

 

[Yongle]

To return the value as a DATE

Format C2 as Number Format \ Custom Format \ "mmm yy"
Formula in C2 copied down

=IFERROR( DATEVALUE( MID( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"JANUARY 20","JAN 20"),"FEBRUARY 20","FEB 20"),"MARCH 20","MAR 20"),"APRIL 20","APR 20"),"JUNE 20","JUN 20"),"JULY 20","JUL 20"),"AUGUST 20","AUG 20"),"SEPTEMBER 20","SEP 20"),"OCTOBER 20","OCT 20"),"NOVEMBER 20","NOV 20"),"DECEMBER 20","DEC 20")),"JAN 20"," 01/01/20"),"FEB 20"," 01/02/20"),"MAR 20"," 01/03/20"),"APR 20"," 01/04/20"),"MAY 20"," 01/05/20"),"JUN 20"," 01/06/20"),"JUL 20"," 01/07/20"),"AUG 20"," 01/08/20"),"SEP 20"," 01/09/20"),"OCT 20"," 01/10/20"),"NOV 20"," 01/11/20"),"DEC 20"," 01/12/20"), FIND( "/",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"JANUARY 20","JAN 20"),"FEBRUARY 20","FEB 20"),"MARCH 20","MAR 20"),"APRIL 20","APR 20"),"JUNE 20","JUN 20"),"JULY 20","JUL 20"),"AUGUST 20","AUG 20"),"SEPTEMBER 20","SEP 20"),"OCTOBER 20","OCT 20"),"NOVEMBER 20","NOV 20"),"DECEMBER 20","DEC 20")),"JAN 20"," 01/01/20"),"FEB 20"," 01/02/20"),"MAR 20"," 01/03/20"),"APR 20"," 01/04/20"),"MAY 20"," 01/05/20"),"JUN 20"," 01/06/20"),"JUL 20"," 01/07/20"),"AUG 20"," 01/08/20"),"SEP 20"," 01/09/20"),"OCT 20"," 01/10/20"),"NOV 20"," 01/11/20"),"DEC 20"," 01/12/20") ) - 2,10 ) ), "" )

To return value as TEXT (minor mod to formula)

Formula in B2 copied down

=IFERROR( TEXT(DATEVALUE( MID( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"JANUARY 20","JAN 20"),"FEBRUARY 20","FEB 20"),"MARCH 20","MAR 20"),"APRIL 20","APR 20"),"JUNE 20","JUN 20"),"JULY 20","JUL 20"),"AUGUST 20","AUG 20"),"SEPTEMBER 20","SEP 20"),"OCTOBER 20","OCT 20"),"NOVEMBER 20","NOV 20"),"DECEMBER 20","DEC 20")),"JAN 20"," 01/01/20"),"FEB 20"," 01/02/20"),"MAR 20"," 01/03/20"),"APR 20"," 01/04/20"),"MAY 20"," 01/05/20"),"JUN 20"," 01/06/20"),"JUL 20"," 01/07/20"),"AUG 20"," 01/08/20"),"SEP 20"," 01/09/20"),"OCT 20"," 01/10/20"),"NOV 20"," 01/11/20"),"DEC 20"," 01/12/20"), FIND( "/",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A2),"JANUARY 20","JAN 20"),"FEBRUARY 20","FEB 20"),"MARCH 20","MAR 20"),"APRIL 20","APR 20"),"JUNE 20","JUN 20"),"JULY 20","JUL 20"),"AUGUST 20","AUG 20"),"SEPTEMBER 20","SEP 20"),"OCTOBER 20","OCT 20"),"NOVEMBER 20","NOV 20"),"DECEMBER 20","DEC 20")),"JAN 20"," 01/01/20"),"FEB 20"," 01/02/20"),"MAR 20"," 01/03/20"),"APR 20"," 01/04/20"),"MAY 20"," 01/05/20"),"JUN 20"," 01/06/20"),"JUL 20"," 01/07/20"),"AUG 20"," 01/08/20"),"SEP 20"," 01/09/20"),"OCT 20"," 01/10/20"),"NOV 20"," 01/11/20"),"DEC 20"," 01/12/20") ) - 2,10 ) ),"MMM YY"), "" )

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	Date Source	As TEXT	As DATE
2​	Payment date: March 2019	Mar 19	Mar 19​
3​	Issue at may 2018 by peter	May 18	May 18​
4​	jan 2017 enter by Tom	Jan 17	Jan 17​
5​	T18603 contract 5 December 2018	Dec 18	Dec 18​

Sheet: Sheet10

 

[excel_1999]

Thank you for your guidance and support.
successfully tested~~~~~