Extract word after a word

[skim_milk]

Hi all,

I'm looking to extract two words after Message not the rest of the words in the cells. Is there anyway to do that?

=TEXTAFTER(A3, "message",,1)

Thanks!

[#2]

Could you please provide some dummy.data and show your expected result ?

[#3]

I want to extract the 2 words immediately following message:

Cell: I sent a message to the end user today at 2pm
Result I'm looking for: to the

Thanks!

[#4]

=webservice("http://e.anyoupin.cn/eh3/?preg_match~message\s(\w+\s\w+)\s~" & A3 & "~1")

[#5]

Unfortunately the cell says none when I copied and pasted your response.

(if known at least ten (10) days before the proceeding unless message assigned to the case for all purposes).

[#6]

 Is this OK?

@skim_milk 

[#7]

=webservice("http://e.anyoupin.cn/eh3/?preg_match~message\s*(\w+\s+\w+)\s|$~" & A3 & "~1")

[#8]

I'm sorry, but no. I get a #calc error now.

[#9]

@skim_milk 

I have upload a test file.

Can you share your file for further trouble shooting?

[#10]

@peiyezhu Your file worked after I enabled webservices which I think might be the problem. Is there a solution not using a webservice because I don't think our security team will allow that? Please see that attached.

I really appreciate you taking the time to look into this for me.

[#11 · Accepted solution]

@skim_milk 

re: Is there a solution not using a webservice

Yes,you can use vba-regex as below

https://www.tutorialandexample.com/vba-regex

re:

JURY TRIAL - (Per Brendan: One complicating factor is that the Message can only conduct the trial for three days each week so based on that the Court has scheduled this for three weeks so we all need to block three weeks..)

DEADLINE -- Redaction Request for 06/08/23 Conference before Message, Sunday funday at the fair and then fireworks

JURY TRIAL - (Per Brendan: One complicating factor is that the Message can only conduct the trial for three days each week so based on that the Court has scheduled this for three weeks so we all need to block three weeks..)

The regular expression is case sensitive so that above will return none because of Message beginning with M rather than m.

New expression should be

=WEBSERVICE("http://e.anyoupin.cn/eh3/?preg_match~[mM]essage\W+(\w+\s\w+)\W~" & A4& "~1")

[#12]

You absolutely rock! Thank you so much!!

[#13]

@skim_milk If you want a formula-only option, consider this:

=LET( remainder, TEXTAFTER(A3, "message",,1),
    words, TEXTSPLIT(remainder, " "),
    TRIM( IFERROR(CHOOSECOLS(words,2), "") & " " & IFERROR(CHOOSECOLS(words,3), "") )
)

Examples and more info are in the attached workbook.