Search text in a cell for a specific letter/number combination

[broncos347]

I have a spreadsheet which contains a column that holds some text, and within that text, there is a priority (which could be A, B, C or D plus the letters will have a suffix of 1, 2, 3, 4 or 5) is there a way to search and extract this information out of the cells of the column in question. The priority could be anywhere within the text.

For example, the text could read as follows “Located at End of Main Building, on vehicle access Route, surface drain gully is blocked, this gully is constantly blocked up during wet weather. C1 NB Historic issues with this area and soak away.” In this instance, I would need to extract the priority of C1.

 

[Akuini]

Could you please provide around 5 rows of sample data along with the expected results?

 

[Peter_SSs]

This is my take on what you are asking. To implement this user-defined function ..
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 PriorityCode(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "[A-D][1-5]"
  If RX.test(s) Then PriorityCode = RX.Execute(s)(0)
End Function

broncos347.xlsm
	A	B
1	Located at End of Main Building, on vehicle access Route, surface drain gully is blocked, this gully is constantly blocked up during wet weather. C1 NB Historic issues with this area and soak away.	C1
2
3	Nothing here
4	D6 is not a code but D5 is	D5
5	b2 is not upper case but A2 is	A2
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=PriorityCode(A1)

 

[broncos347]

See below

Defect-Monitor Log.xlsx
	A	B	C	D
1	Site	Action Tag	Action Comment	Tag Action Condition / Priority
2	Brims Stn	Monitor-Structural Movement | Subsidence | Settlement	Trip hazard due to probable subsidence at foot of ole structure C10 38. B2	B2
3	Mill Stn	Monitor-Structural Movement | Subsidence | Settlement	Between LC16 and LC17 low to level causing ponding. A1	A1
4	Hatfield Stn	Defect - Wear and Tear Etc	Full fracture across ground cover. C1. Faulted April 2022	C1
5	Hatfield Stn	Defect-Graffiti/Vandalism	Located on station name signage nearest country end of Platform. Vinyl application has been peeled off exposing previous TOC livery markings. C1	C1
6	Tree Stn	Defect-Blocked	Located at London End of Main Building, on vehicle access Route, surface drain gully is blocked, this gully is constantly blocked up during wet weather. C1 NB Historic issues with this area and soak away.	C1
7	Tree Stn	Defect - Wear and Tear Etc	Located at entrance end. 1.5m section of railing fencing has reached end of asset life. C1	C1
8	Mount Stn	Defect-Structural Movement | Subsidence | Settlement	1st beam from C End dropped and others moved away from track by up to 70mm resulting in surface to fall away leaving voids. Faulted 30.07.21Historic problem. C1	C1
Sheet1

 

[Peter_SSs]

See below

Thanks for the sample data. My UDF would require a slight change for that sort of data.

Function PriorityCode(s As String) As String
  Dim RX As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\b[A-D][1-5]\b"
  If RX.test(s) Then PriorityCode = RX.Execute(s)(0)
End Function

broncos347.xlsm
	C	D
1	Action Comment	Tag Action Condition / Priority
2	Trip hazard due to probable subsidence at foot of ole structure C10 38. B2	B2
3	Between LC16 and LC17 low to level causing ponding. A1	A1
4	Full fracture across ground cover. C1. Faulted April 2022	C1
5	Located on station name signage nearest country end of Platform. Vinyl application has been peeled off exposing previous TOC livery markings. C1	C1
6	Located at London End of Main Building, on vehicle access Route, surface drain gully is blocked, this gully is constantly blocked up during wet weather. C1 NB Historic issues with this area and soak away.	C1
7	Located at entrance end. 1.5m section of railing fencing has reached end of asset life. C1	C1
8	1st beam from C End dropped and others moved away from track by up to 70mm resulting in surface to fall away leaving voids. Faulted 30.07.21Historic problem. C1	C1
Sheet2

Cell Formulas
Range	Formula
D2:D8	D2	=PriorityCode(C2)

 

[JvdV]

SampleData.xlsx
	A	B	C	D
1	Site	Action Tag	Action Comment	Tag Action Condition / Priority
2	Brims Stn	Monitor-Structural Movement | Subsidence | Settlement	Trip hazard due to probable subsidence at foot of ole structure C10 38. B2	B2
3	Mill Stn	Monitor-Structural Movement | Subsidence | Settlement	Between LC16 and LC17 low to level causing ponding. A1	A1
4	Hatfield Stn	Defect - Wear and Tear Etc	Full fracture across ground cover. C1. Faulted April 2022	C1
5	Hatfield Stn	Defect-Graffiti/Vandalism	Located on station name signage nearest country end of Platform. Vinyl application has been peeled off exposing previous TOC livery markings. C1	C1
6	Tree Stn	Defect-Blocked	Located at London End of Main Building, on vehicle access Route, surface drain gully is blocked, this gully is constantly blocked up during wet weather. C1 NB Historic issues with this area and soak away.	C1
7	Tree Stn	Defect - Wear and Tear Etc	Located at entrance end. 1.5m section of railing fencing has reached end of asset life. C1	C1
8	Mount Stn	Defect-Structural Movement | Subsidence | Settlement	1st beam from C End dropped and others moved away from track by up to 70mm resulting in surface to fall away leaving voids. Faulted 30.07.21Historic problem. C1	C1
Sheet10

Cell Formulas
Range	Formula
D2:D8	D2	=MAP(C2:C8,LAMBDA(s,LET(r,ROW(1:5),x,TEXTSPLIT(s,MID(",.?! ",r,1)),@SORT(XLOOKUP(TOCOL({"A","B","C","D"}&r),x,x,""),,-1))))
Dynamic array formulas.

 

[Peter_SSs]

@JvdV
Suggest SEQUENCE(5) instead of ROW(1:5) so formula still returns the same values if a new row is subsequently inserted above.

 

[broncos347]

@JvdV @Peter_SSs many thanks for your help, both solutions work, just got to decide which one to use now.

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.