Extracting the words around a word or a collection of words

[pratheesh1983]

I am looking for a formula that will help me extract one word before and two word after a specific word or a collection of words (Mentioned in HIGHLIGHT_TEXT column) . This is basically to check the surrounding text value of a matched text that created a wrong alert in our software. I have provided the sample text below for your reference.

HIGHLIGHT_TEXT	SOFFSET	EOFFSET	HIGHLIGHT_TEXT_LINE	Surrounding Text Value (Formula Column)
ALUMINIUM	7​	15​	82 EMPTY ALUMINIUM CANS WITH 202 ENDS (SOT) ,TIGER BLACK CANS,202 ENDS (SOT) SILVER	EMPTY ALUMINIUM CANS
VALVE REMOTE CONTROL	13​	33​	82 ONE SHIPSET VALVE REMOTE CONTROL SYSTEM& TANK GAUGING SYSTEM hs code- 90261029	SHIPSET VALVE REMOTE CONTROL SYSTEM
NARD	103​	106​	110 CB Tilbering FORMTE 005555AOT5900200893654 wwww wwww wwww wwww wwww wwww wwww wwww wwww xxxTPxxx IN BARD /LB	IN BARD /LB

• SOFFSET is the start position of the highlighted text in the complete message text.
• EOFFSET is the end position of the highlighted text in the complete message text.

The column "surrounding the text value" is where the formula needs to be put to extract the value, and while extracting the value, one should not consider if one word preceding or two words following the highlighted text contain either "wwww" or "xxx" as this is not relevant.
So I am looking for a formula that could help me extract information from column HIGHLIGHT_TEXT_LINE," one word before and two words after a specific word or a collection of words (mentioned in the HIGHLIGHT_TEXT column) using SOFFSET or EOFFSET value and not considering 'wwww" or "xxx" as mentioned above. Would really appreciate if some one can help me in this? Thanks

 

[Peter_SSs]

I assume that NARD is supposed to be BARD?

one word before and two word after

Your sample, assuming the expected results are shown, shows one word before and one word after. Please clarify.

• SOFFSET is the start position of the highlighted text in the complete message text.
• EOFFSET is the end position of the highlighted text in the complete message text.

How are they calculated? Here are my calculations in columns C & E

pratheesh1983.xlsm
	A	B	C	D	E	F
1	HIGHLIGHT_TEXT	SOFFSET		EOFFSET		HIGHLIGHT_TEXT_LINE
2	ALUMINIUM	7	10	15	18	82 EMPTY ALUMINIUM CANS WITH 202 ENDS (SOT) ,TIGER BLACK CANS,202 ENDS (SOT) SILVER
3	VALVE REMOTE CONTROL	13	#VALUE!	33	#VALUE!	82 ONE SHIPSET VALVE REMOTE CONTROL SYSTEM& TANK GAUGING SYSTEM hs code- 90261029
4	BARD	103	107	106	110	110 CB Tilbering FORMTE 005555AOT5900200893654 wwww wwww wwww wwww wwww wwww wwww wwww wwww xxxTPxxx IN BARD /LB
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FIND(A2,F2)
E2:E4	E2	=FIND(A2,F2)+LEN(A2)-1

 

[pratheesh1983]

I assume that NARD is supposed to be BARD?

Yes, that's correct it should be 'BARD'

Your sample, assuming the expected results are shown, shows one word before and one word after. Please clarify.

The intended formula we need to put in the column with the header "Surrounding Text Value" and the intended result for the sample I have provided as well for reference Right now, I am doing it manually, so I am looking for any option in Excel that will help pull this information.

How are they calculated? Here are my calculations in columns C & E

pratheesh1983.xlsm
	A	B	C	D	E	F
1	HIGHLIGHT_TEXT	SOFFSET		EOFFSET		HIGHLIGHT_TEXT_LINE
2	ALUMINIUM	7	10	15	18	82 EMPTY ALUMINIUM CANS WITH 202 ENDS (SOT) ,TIGER BLACK CANS,202 ENDS (SOT) SILVER
3	VALVE REMOTE CONTROL	13	#VALUE!	33	#VALUE!	82 ONE SHIPSET VALVE REMOTE CONTROL SYSTEM& TANK GAUGING SYSTEM hs code- 90261029
4	BARD	103	107	106	110	110 CB Tilbering FORMTE 005555AOT5900200893654 wwww wwww wwww wwww wwww wwww wwww wwww wwww xxxTPxxx IN BARD /LB
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=FIND(A2,F2)
E2:E4	E2	=FIND(A2,F2)+LEN(A2)-1

 

[Peter_SSs]

Try this then

pratheesh1983.xlsm
	A			D	E
1	HIGHLIGHT_TEXT			HIGHLIGHT_TEXT_LINE	Surrounding Text Value (Formula Column)
2	ALUMINIUM			82 EMPTY ALUMINIUM CANS WITH 202 ENDS (SOT) ,TIGER BLACK CANS,202 ENDS (SOT) SILVER	EMPTY ALUMINIUM CANS
3	VALVE REMOTE CONTROL			82 ONE SHIPSET VALVE REMOTE CONTROL SYSTEM& TANK GAUGING SYSTEM hs code- 90261029	SHIPSET VALVE REMOTE CONTROL SYSTEM&
4	BARD			110 CB Tilbering FORMTE 005555AOT5900200893654 wwww wwww wwww wwww wwww wwww wwww wwww wwww xxxTPxxx IN BARD /LB	IN BARD /LB
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=LET(s," "&SUBSTITUTE(D2,CHAR(10)," ")&" ",TEXTJOIN(" ",,TAKE(TEXTSPLIT(TRIM(LEFT(s,FIND(" ",s,FIND(A2,s)+LEN(A2)+1)))," "),,-(ROWS(TEXTSPLIT(A2,," "))+2))))

 

[pratheesh1983]

Try this then

pratheesh1983.xlsm
	A			D	E
1	HIGHLIGHT_TEXT			HIGHLIGHT_TEXT_LINE	Surrounding Text Value (Formula Column)
2	ALUMINIUM			82 EMPTY ALUMINIUM CANS WITH 202 ENDS (SOT) ,TIGER BLACK CANS,202 ENDS (SOT) SILVER	EMPTY ALUMINIUM CANS
3	VALVE REMOTE CONTROL			82 ONE SHIPSET VALVE REMOTE CONTROL SYSTEM& TANK GAUGING SYSTEM hs code- 90261029	SHIPSET VALVE REMOTE CONTROL SYSTEM&
4	BARD			110 CB Tilbering FORMTE 005555AOT5900200893654 wwww wwww wwww wwww wwww wwww wwww wwww wwww xxxTPxxx IN BARD /LB	IN BARD /LB
Sheet3

Cell Formulas
Range	Formula
E2:E4	E2	=LET(s," "&SUBSTITUTE(D2,CHAR(10)," ")&" ",TEXTJOIN(" ",,TAKE(TEXTSPLIT(TRIM(LEFT(s,FIND(" ",s,FIND(A2,s)+LEN(A2)+1)))," "),,-(ROWS(TEXTSPLIT(A2,," "))+2))))

Thanks for your response.
But when I used the formula that you shared, I noticed that if there is any line break or the word is concatenated, it throws up an error. Will you be able to help here?

HIGHLIGHT_TEXT	HIGHLIGHT_TEXT_LINE	SURRONDING TEXT VALUE
SEA	A1Nhxva Shbva Sea Diso bornLae	#VALUE!​
ERS	ntral Beadquat ers (QRS), NA, Ge	#VALUE!​

 

[Peter_SSs]

I noticed that if there is any line break or the word is concatenated, it throws up an error.

It is not related to line breaks or concatenation, it is related to upper/lower case letters.
In your original sample everything was in upper case, now it is not.

Try this instead

pratheesh1983.xlsm
	A			D	E
1	HIGHLIGHT_TEXT			HIGHLIGHT_TEXT_LINE	SURRONDING TEXT VALUE
2	SEA			A1Nhxva Shbva Sea Diso bornLae	Shbva Sea Diso
3	ERS			ntral Beadquat ers (QRS), NA, Ge	Beadquat ers (QRS),
Sheet2

Cell Formulas
Range	Formula
E2:E3	E2	=LET(s," "&SUBSTITUTE(D2,CHAR(10)," ")&" ",TEXTJOIN(" ",,TAKE(TEXTSPLIT(TRIM(LEFT(s,FIND(" ",s,SEARCH(A2,s)+LEN(A2)+1)))," "),,-(ROWS(TEXTSPLIT(A2,," "))+2))))