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.

 

[lrobbo314]

This seems to work.

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

Cell Formulas
Range	Formula
B1:B4	B1	=mot(A1)

Function MOT(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "MOT: ((?:\w+\s)+(•|.+))"
    MOT = Replace(.Execute(s)(0).submatches(0), " •", vbNullString)
End With
End Function

 

[PeteWright]

Try this formula, its the result of many small formulas pasted together.

= IF( MID( A1, SEARCH( "MOT:",A1 )+5, 6 )="Exempt", "Exempt", LEFT(RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4), SEARCH( " ", RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4) )+4) )

_______________________________________
EDIT:
What it does:
1. Search, if there is "MOT:" in the text and if that's true it checks if there is "Exempt" after "MOT:" followed by a space
2. If there's no "MOT:", it assumes that there's a MONTH followed by a SPACE Character and a YEAR (which must be 4 characters in length). So it looks for the second SPACE Character and extracts the month and the year.

Keep in mind that this formula only works with text that is strictly as you described, no exclusions allowed.

If you want it a bit more flexible, you should consider a user defined formula (VBA) that uses Regular Expressions (RegEx) or something similar.

Let me know if it works for you.

 

[alansidman]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, "MOT:", "•"), type text}})
in
    #"Extracted Text Between Delimiters"

 

[Bosquedeguate]

Is this the result you are expecting

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

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,SEARCH("MOT:",A2),IFERROR(SEARCH("•",MID(A2,SEARCH("MOT:",A2),LEN(A2))),LEN(A2))-2)

 

[Bosquedeguate]

Try this formula, its the result of many small formulas pasted together.

= IF( MID( A1, SEARCH( "MOT:",A1 )+5, 6 )="Exempt", "Exempt", LEFT(RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4), SEARCH( " ", RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4) )+4) )

_______________________________________
EDIT:
What it does:
1. Search, if there is "MOT:" in the text and if that's true it checks if there is "Exempt" after "MOT:" followed by a space
2. If there's no "MOT:", it assumes that there's a MONTH followed by a SPACE Character and a YEAR (which must be 4 characters in length). So it looks for the second SPACE Character and extracts the month and the year.

Keep in mind that this formula only works with text that is strictly as you described, no exclusions allowed.

If you want it a bit more flexible, you should consider a user defined formula (VBA) that uses Regular Expressions (RegEx) or something similar.

Let me know if it works for you.

I was thinking use of a regular expression would make this very easy!!

 

[MajorTom]

This seems to work.

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

Cell Formulas
Range	Formula
B1:B4	B1	=mot(A1)

Function MOT(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "MOT: ((?:\w+\s)+(•|.+))"
    MOT = Replace(.Execute(s)(0).submatches(0), " •", vbNullString)
End With
End Function

Thank you. I'll try this out.
I was hoping to solve with formula but I'll definitely try this as an alternative.

 

[MajorTom]

Try this formula, its the result of many small formulas pasted together.

= IF( MID( A1, SEARCH( "MOT:",A1 )+5, 6 )="Exempt", "Exempt", LEFT(RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4), SEARCH( " ", RIGHT( A1, LEN(A1)-SEARCH( "MOT:",A1 )-4) )+4) )

_______________________________________
EDIT:
What it does:
1. Search, if there is "MOT:" in the text and if that's true it checks if there is "Exempt" after "MOT:" followed by a space
2. If there's no "MOT:", it assumes that there's a MONTH followed by a SPACE Character and a YEAR (which must be 4 characters in length). So it looks for the second SPACE Character and extracts the month and the year.

Keep in mind that this formula only works with text that is strictly as you described, no exclusions allowed.

If you want it a bit more flexible, you should consider a user defined formula (VBA) that uses Regular Expressions (RegEx) or something similar.

Let me know if it works for you.

Thank you. This seems to work I think I covered all the variations in the text in my OP sample but will test it on a larger data sample to confirm.

 

[MajorTom]

Power Query Solution

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, "MOT:", "•"), type text}})
in
    #"Extracted Text Between Delimiters"

Thank you.
I am hoping to solve the problem with formula but will look at this as an option.

 

[MajorTom]

Is this the result you are expecting

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

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2,SEARCH("MOT:",A2),IFERROR(SEARCH("•",MID(A2,SEARCH("MOT:",A2),LEN(A2))),LEN(A2))-2)

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?