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
[TABLE="width: 300"]
<tbody>[TR]
[TD]Date Source[/TD]
[TD]Date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Payment date: March 2019[/TD]
[TD]Mar 19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Issue at may 2018 by peter[/TD]
[TD]May 18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jan 2017 enter by Tom[/TD]
[TD]Jan 17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T18603 contract 5 December 2018[/TD]
[TD]Dec 18[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Date Source[/td][td]As TEXT[/td][td]As DATE[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Payment date: March 2019[/td][td]Mar 19[/td][td]

Mar 19​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Issue at may 2018 by peter[/td][td]May 18[/td][td]

May 18​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]jan 2017 enter by Tom[/td][td]Jan 17[/td][td]

Jan 17​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]T18603 contract 5 December 2018[/td][td]Dec 18[/td][td]

Dec 18​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet10[/td][/tr][/table]

 

[excel_1999]

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