Google Sheets extract string between strings

Question

So, I've imported emails to google sheets and I'm trying to extract some info.

e.g.: "Bla Bla Bla Ref: 338256 Clients: André Paulos, Beatriz Silva Thank you" To retrieve the value of "Ref" I'm using: =ArrayFormula(REGEXEXTRACT(A2:A,"[\n\r].Ref: \s([^Clients]*)"))

If I use the same for extracting Clients' names, I get the results with a lot of missing characters. For the above example, it returns "André P". (Using: =ArrayFormula(REGEXEXTRACT(A2:A,"[\n\r].Clients: \s([^Thank*)"))

What am I missing? Thanks in advance

JPV · Accepted Answer · 2020-06-17 16:38:35Z

2

See if this helps

 =Arrayformula(if(len(A2:A), trim(regexextract(A2:A, "([^\:]*),")),))

or

=Arrayformula(if(len(A2:A), regexextract(A2:A, "Clients: (.*),")),))

edited Jun 17, 2020 at 16:38

answered Jun 17, 2020 at 16:31

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Whoopcg Over a year ago

The first one worked! I just had to change the first comma for "Thank". Thank you very much!

Collectives™ on Stack Overflow

Google Sheets extract string between strings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related