Extracting specific text from a thread

[MajorTom]

Hi, I'm really hoping someone could assist with a method to deal with an issue I'm having with some text extraction.

I have a column of data and need to extract the MOT information. This could be "Exempt" or a date in the format of "Month Year" and this is the only part of the full text string I want to return to another column.

The data can vary and I have no control over this - this is a sample of rows of 4 differing formats I have had so far:

Registration No: AN01 ABC Tax: N/A MOT: Exempt • Formerly owned by 2 people • Original signage • History is unknown • Many receipts etc •

Registration No: BR07 NIP Tax: 3 months • MOT: December 2021 • Current ownership since 2009 • Refurbished •

Registration No: BTU 218X Tax: 6 Months • MOT: July 2022 Much restored with a lot of history

Registration No: LUV 197T Tax: N/A CC: 175cc MOT: Exempt Motorcycle • Restored • Lightweight

I have had some success with the following formula: =IF(TRIM(SUBSTITUTE(MID(A2,SEARCH("MOT: ",A2)+LEN("MOT: "),9),"•"," "))="Exempt","Exempt",TRIM(SUBSTITUTE(MID(A2,SEARCH("MOT: ",A2)+LEN("MOT: "),9),"•"," "))) but the problem I'm having is that there is not always a bullet point to use in counting my number of characters for the MID formula and using a fixed number of characters misses the end of the longer months or gives me too many if the the status is exempt.

Any ideas or a different approach would be appreciated.

Thank you.

 

[Bosquedeguate]

Thank you for the reply. Almost what I'm after but I would need to tidy the results in cell B4 to just "July 2022" and B5 to just "Exempt".

I have not used regular expression before - would that be using VBA?

 

[Bosquedeguate]

Yes, to use regular expressions you would have to write your function as VBA code. Excel string formulas do not have an provision for using regular expressions.

Here is an improved version of the formula that eliminated the "MOT: " for the resulting string.
=MID(MID(A2,SEARCH("MOT:",A2),IFERROR(SEARCH("•",MID(A2,SEARCH("MOT:",A2),LEN(A2))),LEN(A2))-2),5,99)

 

[Phuoc]

Try this:

Book1
	A	B
1	Original Text
2	Registration No: AN01 ABC Tax: N/A MOT: Exempt • Formerly owned by 2 people • Original signage	Exempt
3	Registration No: BR07 NIP Tax: 3 months • MOT: December 2021 • Current ownership since 2009 • Refurbished •	December 2021
4	Registration No: BTU 218X Tax: 6 Months • MOT: July 2022 Much restored with a lot of history	July 2022
5	Registration No: LUV 197T Tax: N/A CC: 175cc MOT: Exempt Motorcycle • Restored • Lightweight	Exempt
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNTIF(A2,"*MOT: Exempt*"),"Exempt",TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("MOT: ",A2)+LEN("MOT:"),"")," ",REPT(" ",50)),100)))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

 

[MajorTom]

Yes, to use regular expressions you would have to write your function as VBA code. Excel string formulas do not have an provision for using regular expressions.

Here is an improved version of the formula that eliminated the "MOT: " for the resulting string.
=MID(MID(A2,SEARCH("MOT:",A2),IFERROR(SEARCH("•",MID(A2,SEARCH("MOT:",A2),LEN(A2))),LEN(A2))-2),5,99)

Ok, thank you. I'll have a look at VBA.

Also thanks for the updated formula.

 

[MajorTom]

Try this:

Book1
	A	B
1	Original Text
2	Registration No: AN01 ABC Tax: N/A MOT: Exempt • Formerly owned by 2 people • Original signage	Exempt
3	Registration No: BR07 NIP Tax: 3 months • MOT: December 2021 • Current ownership since 2009 • Refurbished •	December 2021
4	Registration No: BTU 218X Tax: 6 Months • MOT: July 2022 Much restored with a lot of history	July 2022
5	Registration No: LUV 197T Tax: N/A CC: 175cc MOT: Exempt Motorcycle • Restored • Lightweight	Exempt
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNTIF(A2,"*MOT: Exempt*"),"Exempt",TRIM(LEFT(SUBSTITUTE(REPLACE(A2,1,SEARCH("MOT: ",A2)+LEN("MOT:"),"")," ",REPT(" ",50)),100)))

Thank you, I think this is as close as it'll get without VBA.

 

[Fluff]

Thanks for updating your profile.
As you have 365 another formula option is

Fluff.xlsm
	A	B
1	Original Text
2	Registration No: AN01 ABC Tax: N/A MOT: Exempt • Formerly owned by 2 people • Original signage	Exempt
3	Registration No: BR07 NIP Tax: 3 months • MOT: December 2021 • Current ownership since 2009 • Refurbished •	December 2021
4	Registration No: BTU 218X Tax: 6 Months • MOT: July 2022 Much restored with a lot of history	July 2022
5	Registration No: LUV 197T Tax: N/A CC: 175cc MOT: Exempt Motorcycle • Restored • Lightweight	Exempt
Master

Cell Formulas
Range	Formula
B2:B5	B2	=LET(x,TEXTAFTER(A2,"MOT: "),IF(LEFT(x,6)="Exempt","Exempt",TEXTBEFORE(x," ",2)))

 

[MajorTom]

What version of Excel are you using?

I suggest that you update your Account details (or 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’)

OK, thanks. It should all be updated now.

 

[MajorTom]

Thanks for updating your profile.
As you have 365 another formula option is

Fluff.xlsm
	A	B
1	Original Text
2	Registration No: AN01 ABC Tax: N/A MOT: Exempt • Formerly owned by 2 people • Original signage	Exempt
3	Registration No: BR07 NIP Tax: 3 months • MOT: December 2021 • Current ownership since 2009 • Refurbished •	December 2021
4	Registration No: BTU 218X Tax: 6 Months • MOT: July 2022 Much restored with a lot of history	July 2022
5	Registration No: LUV 197T Tax: N/A CC: 175cc MOT: Exempt Motorcycle • Restored • Lightweight	Exempt
Master

Cell Formulas
Range	Formula
B2:B5	B2	=LET(x,TEXTAFTER(A2,"MOT: "),IF(LEFT(x,6)="Exempt","Exempt",TEXTBEFORE(x," ",2)))

Thank you. This looks like it will solve the issue I'm having.

 

[Fluff]

Glad we could help & thanks for the feedback.