Copy month and year text only from long sentence text

[lakshmipathi123]

Hi Experts,

I need copy only month and year text from below sentence. I tried with left, right, and mid function but month and year place changes.


Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE
Provision for Revenue Rent - Sep'20

63013-JUN'20 Being revenue rent for Jallandar store

 

[lakshmipathi123]

Not surprising there are errors as this is different data and a different requirement - which makes it quite a different problem!


.. and sometimes "-" which was not mentioned before.

Also, my previous suggestion was based on your original samples from which I made the assumption


In any case, try this

lakshmipathi123.xlsm
	A	B
1	Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE	JUN20
2	Provision for Revenue Rent - Sep'20	Sep20
3	63013-JUN'20 Being revenue rent for Jallandar store	JUN20
4	63013-APR-19 TO MAR-20	APR19 TO MAR20
5	Pune - HC - JUL20 TO SEP20	JUL20 TO SEP20
6	Provision for Dec-20||Dec 20||1434||63013	Dec20
MonthYear (3)

Cell Formulas
Range	Formula
B1:B6	B1	=LET(x,LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,SEQUENCE(LEN(A1)),6),"'"," "),"-"," "),6),y,IF(ISNUMBER(DATEVALUE(1&x)),IF(ISNUMBER(SEARCH(LEFT(x,3),"janfebmaraprmayjunjulaugsepoctnovdec")),x,""),""),TEXTJOIN(" TO ",1,UNIQUE(SUBSTITUTE(FILTER(y,y<>"","")," ",""))))

BTW, thanks for updating your profile.

Peter - Thanks a lot for your quick response. Formula working like a charm Many thanks your help.

Grateful if you could help me in fixing below requirement as well.

In addition to above, one more separate request similar to my earlier question. I just need to copy 5 digits account number (always numeric) from the following sentences

1) Provision for Dec-20||Dec 20||1434||63013 - Need to capture only "63013" (any 5 digit numeric account)
2) 63001-APR-19 TO MAR-20 - Need to capture and copy only "63001"

Thanks,
Lakshmipathi

 

[KP117]

An alternative one, try this at B1,
=TEXTJOIN(" TO ",TRUE,IFERROR(UPPER(MID(SUBSTITUTE(SUBSTITUTE($A1,"-",""),"'",""),SORT(SEARCH({"Jan";"Feb";"Mar";"Apr";"May";"Jun";"Jul";"Aug";"Sep";"Oct";"Nov";"Dec"},SUBSTITUTE(SUBSTITUTE($A1,"-",""),"'",""))),5)),""))

To extract the 5 digits account number, try this at C1,
=IFERROR(MID($A1,FIND("11111",TEXTJOIN("",TRUE,IFERROR(IF(MID($A1,SEQUENCE(1,LEN(A1),1,1),1)+0>=0,1),"-"))),5),"")

 

[Peter_SSs]

I just need to copy 5 digits account number

This suggestion, as with KP117's suggestion assumes that there will not be numeric sequences of more than 5 digits in the text being evaluated.

lakshmipathi123.xlsm
	A		C
1			Acc. No.
2	Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE
3	Provision for Revenue Rent - Sep'20
4	63013-JUN'20 Being revenue rent for Jallandar store		63013
5	63013-APR-19 TO MAR-20		63013
6	Pune - HC - JUL20 TO SEP20
7	Provision for Dec-20||Dec 20||1434||63013		63013
MonthYear (3)

Cell Formulas
Range	Formula
C2:C7	C2	=MID(A2,FIND(11111,CONCAT(--ISNUMBER(MID(A2,SEQUENCE(LEN(A2)),1)+0))&211111),5)

 

[lakshmipathi123]

This suggestion, as with KP117's suggestion assumes that there will not be numeric sequences of more than 5 digits in the text being evaluated.

lakshmipathi123.xlsm
	A		C
1			Acc. No.
2	Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE
3	Provision for Revenue Rent - Sep'20
4	63013-JUN'20 Being revenue rent for Jallandar store		63013
5	63013-APR-19 TO MAR-20		63013
6	Pune - HC - JUL20 TO SEP20
7	Provision for Dec-20||Dec 20||1434||63013		63013
MonthYear (3)

Cell Formulas
Range	Formula
C2:C7	C2	=MID(A2,FIND(11111,CONCAT(--ISNUMBER(MID(A2,SEQUENCE(LEN(A2)),1)+0))&211111),5)

Thank you, Peter Its all set now.

 

[Peter_SSs]

You're welcome. Cheers.