Extracting data from a field within a text

[pratheesh1983]

I have an excel table with a cell that contains message data, and the message data is divided into multiple fields that can be easily identified using the field ID format, i.e., "FEILD NO." Now have two requirements: first, to pull the complete data content of a specific field; and second, from the same field, to pull three words before the text (which would be mentioned in a separate cell) and three words after the text. If there are no words (or letters) before or after that specific word in the specific field, then there is no need to pull the content. Hence, to extract these two pieces of information, I am looking for a formula in Excel that could extract this information based on the information in different cells. I have provided below an example and the last two columns where I am looking for a formula, but for reference, I have provided the content that needs to be extracted. I would appreciate support in finding a formula for this. Thanks

Message Data	Field ID	Text To be searched	Field Content (**FORMULA**)	Surrounding words around the text (**FORMULA**)
:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 13976111 RAHUL JURASIC INDUSTRIES :PD1:SUSANA GRACIA THOMAS JOHN 7892567 56789	PD1	ANA	SUSANA GRACIA THOMAS 7892567 56789	SUSANA GRACIA THOMAS
:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 13976111 RAHUL JURASIC INDUSTRIES :PD1:SUSANA GRACIA THOMAS JOHN 7892567 56789	B2	RAHUL	13976111 RAHUL JURASIC INDUSTRIES	RAHUL JURASIC INDUSTRIES
:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 219662341 ELLE LARA, FRANCISCO JAVIER JOHN :PD:SUSANA GRACIA MINGUILLON 7892567 56789	B2	E LARA	219662341 ELLE LARA, FRANCISCO JAVIER JOHN	ELLE LARA, FRANCISCO JAVIER JOHN

 

[pratheesh1983]

Field Tag format would be ":Tag Name:"

 

[Peter_SSs]

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’)

Could you use a User-Defined Function like this?

Function GetField(s As String, FID As String, Txt As String, Optional TextOnly As Boolean) As String
  Dim RX As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(:" & FID & ":)([^:]*?" & Txt & "[^:]*)(?=:|$)"
  If RX.Test(s) Then
    GetField = RX.Execute(s)(0).SubMatches(1) '& RX.Execute(s)(0).SubMatches(2)
    If TextOnly Then
      RX.Global = True
      RX.Pattern = "\d"
      GetField = Application.Trim(RX.Replace(GetField, ""))
    End If
  End If
End Function

pratheesh1983.xlsm
	A	B	C	D	E
1	Message Data	Field ID	Text To be searched	Field	Words
2	:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 13976111 RAHUL JURASIC INDUSTRIES :PD1:SUSANA GRACIA THOMAS JOHN 7892567 56789	PD1	ANA	SUSANA GRACIA THOMAS JOHN 7892567 56789	SUSANA GRACIA THOMAS JOHN
3	:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 13976111 RAHUL JURASIC INDUSTRIES :PD1:SUSANA GRACIA THOMAS JOHN 7892567 56789	B2	RAHUL	13976111 RAHUL JURASIC INDUSTRIES	RAHUL JURASIC INDUSTRIES
4	:OP:0213025 ABC BANK TBK SUBROTO :OI:XYZYPESRXXX AMO UNIVERSE 20-01 :B1:PKRSTDXXX WYNE NETWORK CHARLES BUILDING ABC :B2: 219662341 ELLE LARA, FRANCISCO JAVIER JOHN :PD:SUSANA GRACIA MINGUILLON 7892567 56789	B2	E LARA	219662341 ELLE LARA, FRANCISCO JAVIER JOHN	ELLE LARA, FRANCISCO JAVIER JOHN
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=GetField(A2,B2,C2)
E2:E4	E2	=GetField(A2,B2,C2,TRUE)