REGEX_Match return index

[danBrow]

Hi all,

I have a problem similar to this silly example:

I have a text in the field [a]:

"hello how are you would you like to buy a car no thanks"

I'm looking in this field [a] for a word [x] next to another [y] like this

^.*(?:buy)\W+(?:\w+\W+){0,5}?(?:car)([\w+\W+])*

In a generic way I'm doing this:

REGEX_Match([a],"^.*(?:"+[x]+")\W+(?:\w+\W+){0,5}?(?:"+[y]+")([\w+\W+])*"

As a return I get the true value,

I need to receive the index of the word where the match occurred, in this example I need to receive the number 9.

how do i receive this number?

Thanks in advance to anyone that can help!

[jrgo · Accepted solution]

Hi @danBrow 

Try this formula

CountWords(
REGEX_REPLACE([a],"^(.*)(?:"+[x]+")\W+(?:\w+\W+){0,5}?(?:"+[y]+")([\w+\W+])*", '$1')
) + 1

[danBrow]

Hi

worked perfectly

thank you so much!