Errors in SEARCH() results

[VictorV]

Hi everyone,

I am having trouble with a SEARCH() formula that isn't acting as I would expect.

I have this long text in the field "FORMULE" :

"1: Pratiquer une activité
2: Me consacrer
3: Vivre des expériences
4: Pratiquer un sport"

I want to search for the position of this string : 

"Vivre des expériences"

Here is my formula : LEFT(FORMULE,FIND("Vivre des expériences",FORMULE))

And here is my result : 

"1: Pratiquer une activité
2: Me consacrer
3: Vi"

-> The end string finishes by "Vi" which is one caracter to long, it should finish by "V" only... It is an example but it changes when you look for the other words, other example, when I search for "Pratiquer un sport" it gives me this result :

"1: Pratiquer une activité
2: Me consacrer
3: Vivre des expériences
4: Pra"

-> It is now 2 caracters too long, it shloud finish by "P" only

Do you have any solution about this ? I am surely missing something...

Thanks a lot for your help

[Sho · Accepted solution]

Hi @VictorV,

The results are different when the order is changed, it is indeed a strange behavior.
Apparently, multi-line text seems to be the cause.

How about this formula instead?
Replace the match string and following strings with a single character and get the length of the string.

LEFT(FORMULE,LEN(REGEX_REPLACE(FORMULE,"Vivre des expériences" & "(.|\n)*","@")))

[VictorV]

Hi @Sho 

Thanks a lot, it works perfectly !

Can you explain how the formula works ?