pratheesh1983
Board Regular
- Joined
- Aug 13, 2015
- Messages
- 55
- Office Version
- 365
- Platform
- Windows
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.
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
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