I am having an issue using the FindString() command. I know that it finds the first occurrence of the Target in a string, but my issue is that my Target is a column of values. Some of these values have words that are repeated; for instance they may have words like “Credit Card” and “Card”
I have data like this:
Header | Full Text | Body Text (Expected) | Body Text (Actual) |
Credit Card | -Credit Card: Should be used for business only. -Card: Cards for identification should be up to date. | Should be used for business only. | Should |
Card | -Credit Card: Should be used for business only. -Card: Cards for identification should be up to date. | Cards for identification should be up to date. | Should be used for business only. -Card: Cards for identification should be up to date. |
I’ve tried to use FindString([Full Text], [Header]) to find the index of “Credit Card” and “Card” and use Substring() to get the text from the first header to the second header in order to have the output in the Body Text (Expected) column but I cannot get it to work.
The substring command I’ve used is: Substring([Full Text],[Header_Index]+[Header_Length]+1,
[Next_Header_Index]-[Header_Index])
Is there any way to use FindString() or a similar command so that when I find the index of [Header], it corresponds to the full and unique header text, and not just the first occurrence?
In the current state, when using FindString([Full Text],[Header]) to find the Header Index, it gives me 8 (which is where it is within “Corporate Card:”), but I need it to be 48, where “Card:” begins.
Thank you!
