I’m using the Search function to locate where I want to extract text using the Left String function, I just don’t know how to combine the two functions. For example within one row of data there’s text with multiple delimiters within the string. Search finds the second delimiter correctly. Now I want to grab the text that precedes the delimiter.
Do you want the string-part between the first and second delimiters, or the string on left side of the second delimiter (together with the first delimiter)? Here is a comparison of karolus’s REGEX solution and the usage of the cell functions LEFT, SEARCH:
Second delimiter.ods (9.3 KB)
1 Like
Hallo
if, for example your delimiter is a ,comma:
=REGEX(A1;"^([^,]+,[^,]+),.*$";"$1")Thank you, I don’t understand what you created but I will try using it. The delimiter is a COLON “:” Once I extract the text data that precedes the 2nd colon, I will then extract the text data the follows the colon.
Can you help me understand your solution so I may learn from it?
No I cannot help for every detail about »regexes«… but there are thousands of tutorials about the topic, ask your friendly search-engine or use the help about the regex.function
The pattern above from left to right:
^ # denotes the start of the string
( # opens a group which can be later referenced in the replace-field #1
[^,] # any single char **except** ,comma …
+ # the previous pattern one or more times repeated
, # obviously the (first) comma
[^,]+ # here its again »any char except comma, repeated one or more times…
) # close the group which was opened by #1
, # the second comma
. # any char …
* # … zero or more times until …
$ # …the end of the string
fortunatly the :colon has no special meaning in this context, so use simply:
=REGEX(A1;"^([^:]+:[^:]+):.*$";"$1") # left of second colon
=REGEX(A1;"^([^:]+:[^:]+):(.*)$";"$2") # right of second colon
2 Likes
Thank you so much.
Our “AI troll” has awakened again?! And is spreading its bullshit!?
