Using a command to find the index of a whole String from one column in another column

[ryanhartman32]

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!

[Felipe_Ribeir0 · Accepted solution]

Hi @ryanhartman32 

I believe that for this case it is better to use regex. The formula is capturing every text between the Header and the first dot.

[ryanhartman32]

Oh wow that worked perfectly, thank you Felipe!