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

 

[kweaver]

The year sometimes has a single quote and sometimes not?
Is the month always a 3-character abbreviation?
If the mo and yr are in there, would there always be a - before and after?

There must be an easier way, but this maybe does it with a helper column.

Book17
	A	B	C
1	Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE	HYDINORBITMALL-1315-JUN20-REVENUESHARE	JUN20
2	Provision for Revenue Rent - Sep'20	PROVISIONFORREVENUERENT-SEP20	SEP20
3	For - JAN21 - Payment	FOR-JAN21-PAYMENT	JAN21
4	63013 - JUN'20 - Being revenue rent for Jallandar store	63013-JUN20-BEINGREVENUERENTFORJALLANDARSTORE	JUN20
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=UPPER(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ",""))
C1:C4	C1	=IF(1=LEN(B1)-LEN(SUBSTITUTE(B1,"-","")),RIGHT(B1,5),MID(B1,FIND("@",SUBSTITUTE(B1,"-","@",LEN(B1)-LEN(SUBSTITUTE(B1,"-",""))),1)-5,5))

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

One way would be to use a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function MonthYr(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = "\b(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)'?\d{2,}"
  If RX.Test(s) Then MonthYr = Replace(RX.Execute(s)(0), "'", "")
End Function

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
MonthYear

Cell Formulas
Range	Formula
B1:B3	B1	=MonthYr(A1)

@kweaver
That formula approach does not appear to work for the third example in post 1?

 

[kweaver]

@kweaver
That formula approach does not appear to work for the third example in post 1?

Yep, I knew that...that's why I edited the third one to have dashes in it. I personally like the UDF, and the OP didn't say with or without VBA...So, stars to you.

 

[Peter_SSs]

Assuming Excel 365 is available and with the LET function then a worksheet formula option could be as follows. Assuming a maximum of one MonthYr in the text in each cell.

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
MonthYear (2)

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

 

[kweaver]

Great use of the new LET function. I've been trying to come up with good examples and this is REALLY good! Thanks, Peter_SSs!!

 

[lakshmipathi123]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Updated... thanks

 

[lakshmipathi123]

The year sometimes has a single quote and sometimes not? Yes
Is the month always a 3-character abbreviation? Yes

The year sometimes has a single quote and sometimes not?
Is the month always a 3-character abbreviation?
If the mo and yr are in there, would there always be a - before and after?

There must be an easier way, but this maybe does it with a helper column.

Book17
	A	B	C
1	Hyd Inorbit Mall - 1315 - JUN20-REVENUE SHARE	HYDINORBITMALL-1315-JUN20-REVENUESHARE	JUN20
2	Provision for Revenue Rent - Sep'20	PROVISIONFORREVENUERENT-SEP20	SEP20
3	For - JAN21 - Payment	FOR-JAN21-PAYMENT	JAN21
4	63013 - JUN'20 - Being revenue rent for Jallandar store	63013-JUN20-BEINGREVENUERENTFORJALLANDARSTORE	JUN20
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=UPPER(SUBSTITUTE(SUBSTITUTE(A1,"'","")," ",""))
C1:C4	C1	=IF(1=LEN(B1)-LEN(SUBSTITUTE(B1,"-","")),RIGHT(B1,5),MID(B1,FIND("@",SUBSTITUTE(B1,"-","@",LEN(B1)-LEN(SUBSTITUTE(B1,"-",""))),1)-5,5))

If the mo and yr are in there, would there always be a - before and after?

Assuming Excel 365 is available and with the LET function then a worksheet formula option could be as follows. Assuming a maximum of one MonthYr in the text in each cell.

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
MonthYear (2)

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

Thank you Peter, It is working fine with few errors. Could you please help me fixing the following?

#SPILL! error for the following texts..

1) 63013-APR-19 TO MAR-20 - Need to capture "APR-19 TO MAR-20"
2) Pune - HC - JUL20 TO SEP20 - Need to capture "JUL20 TO SEP20"
3) Provision for Dec-20||Dec 20||1434||63013 - Need to capture only "Dec-20"

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

 

[Peter_SSs]

It is working fine with few errors. Could you please help me fixing the following?

#SPILL! error for the following texts..

1) 63013-APR-19 TO MAR-20 - Need to capture "APR-19 TO MAR-20"
2) Pune - HC - JUL20 TO SEP20 - Need to capture "JUL20 TO SEP20"
3) Provision for Dec-20||Dec 20||1434||63013 - Need to capture only "Dec-20"

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

The year sometimes has a single quote and sometimes not? Yes

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

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

Assuming a maximum of one MonthYr in the text in each cell.

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.