Pulling data from specific Feild

[pratheesh1983]

I'm looking for a formula in Excel that can extract field content from a field tag that is specified in a cell that is mentioned next to the text. I have provided a message sample that would explain the requirement in more detail.

The formula that I'm using is giving me a blank value in some cells; this may be because of a line break in the respective field from which the data needs to be extracted. I would appreciate it if someone could help me fix this issue.

A	B	C (Formula to be added in this column to extract the data based on field ID mentioned in B column.)
TEXT	FEILD	FEILD CONTENT
AMAN SOLUTIONS LTD 110, MG ROAD BANGALORE 03 INDIA :YU1:689009882 XYZ OPS :CN2: GEORGE THOMAS	CN2	GEORGE THOMAS
ABC LTD SILICON, MG ROAD BANGALORE 03 INDIA :YU1: BOLONO :CN2: MARTIN :CN1: JACOB ALEX :PB3: JOSE ELEC	CN1	JACOB ALEX

Formula Used: =TRIM(LEFT(SUBSTITUTE(REPLACE($A2,1,SEARCH($B2,$A2)+LEN($B2),""),CHAR(10),REPT(" ",100)),100))

 

[rlv01]

There are probably a lot of ways to get the result you want. One way:

tmp.xlsm
	A	B	C
1	TEXT	FIELD	FIELD CONTENT
2	🇵🇰 AMAN SOLUTIONS LTD 110, MG ROAD BANGALORE 03 INDIA :YU1:689009882 XYZ OPS :CN2: GEORGE THOMAS	CN2	GEORGE THOMAS
3	🇵🇰 ABC LTD SILICON, MG ROAD BANGALORE 03 INDIA :YU1: BOLONO :CN2: MARTIN :CN1: JACOB ALEX :PB3: JOSE ELEC	CN1	JACOB ALEX
4	🇵🇰 AMAN SOLUTIONS LTD 110, MG ROAD BANGALORE 03 INDIA :YU1:689009882 XYZ OPS :CN2: GEORGE THOMAS :CN1: JACOB ALEX :PB3: JOSE ELEC	PB3	JOSE ELEC
5	🇵🇰 ABC LTD SILICON, MG ROAD BANGALORE 03 INDIA :YU1: BOLONO :CN2: MARTIN :CN1: JACOB ALEX :PB3: JOSE ELEC	XYZ	Field 'XYZ' not found
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(TRIM(MID(SUBSTITUTE(MID(TRIM(A2)&":",FIND(":" & B2 & ":",A2)+1,LEN(A2)),":",REPT(" ",100)),10,200)),"Field '" & B2 & "' not found")

 

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

 

[pratheesh1983]

There are probably a lot of ways to get the result you want. One way:

tmp.xlsm
	A	B	C
1	TEXT	FIELD	FIELD CONTENT
2	🇵🇰 AMAN SOLUTIONS LTD 110, MG ROAD BANGALORE 03 INDIA :YU1:689009882 XYZ OPS :CN2: GEORGE THOMAS	CN2	GEORGE THOMAS
3	🇵🇰 ABC LTD SILICON, MG ROAD BANGALORE 03 INDIA :YU1: BOLONO :CN2: MARTIN :CN1: JACOB ALEX :PB3: JOSE ELEC	CN1	JACOB ALEX
4	🇵🇰 AMAN SOLUTIONS LTD 110, MG ROAD BANGALORE 03 INDIA :YU1:689009882 XYZ OPS :CN2: GEORGE THOMAS :CN1: JACOB ALEX :PB3: JOSE ELEC	PB3	JOSE ELEC
5	🇵🇰 ABC LTD SILICON, MG ROAD BANGALORE 03 INDIA :YU1: BOLONO :CN2: MARTIN :CN1: JACOB ALEX :PB3: JOSE ELEC	XYZ	Field 'XYZ' not found
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(TRIM(MID(SUBSTITUTE(MID(TRIM(A2)&":",FIND(":" & B2 & ":",A2)+1,LEN(A2)),":",REPT(" ",100)),10,200)),"Field '" & B2 & "' not found")

Thanks!
There is a scenario where we have field tags starts with < and ends with > (instead of ":") in those case what changes we need to make in the formula.

Example:

<ABC2022>
GEORGE THOMAS

 

[Fluff]

How about

=LET(x,TEXTSPLIT(SUBSTITUTE(A2,CHAR(10),""),{":","<",">"}),IFNA(TRIM(TAKE(DROP(x,,MATCH(B2,x,0)),,1)),"No match"))

 

[rlv01]

There is a scenario where we have field tags starts with < and ends with > (instead of ":") in those case what changes we need to make in the formula.

Though perhaps not as elegant as @Fluff's solution, you could extending my earlier example this way.

=IFERROR(LET(Field,B2,Text,SUBSTITUTE(SUBSTITUTE(A2,">",":"),"<",":"),TRIM(MID(SUBSTITUTE(MID(TRIM(Text)&":",FIND(":" & Field & ":",Text)+1,LEN(Text)),":",REPT(" ",100)),10,200))),"Field '" & B3 & "' not found")