Help

Errors in SEARCH() results

Topic Labels: Formulas
Solved
Jump to Solution
1316 2
cancel
Showing results for 
Search instead for 
Did you mean: 
VictorV
4 - Data Explorer
4 - Data Explorer

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

1 Solution

Accepted Solutions
Sho
11 - Venus
11 - Venus

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)*","@")))

 

See Solution in Thread

2 Replies 2
Sho
11 - Venus
11 - Venus

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)*","@")))

 

Hi @Sho 

Thanks a lot, it works perfectly !

Can you explain how the formula works ?