Return text string in excel between a specified word and character

Question

I am in need of a formula that will return a varying number of characters between specified text Network: and a special character ; in excel. As an example see below

Product Name: XXXX; Cover: Full; Holiday Cover: Not Applicable; Additional Cover: Not Applicable; Network: Essential Access; Excess: £0; Out Of Network: X% (no limit);

I want the value "Essential Access" only. Key points to note are the value returned will vary in length and its position within the string will also vary.

JvdV · Accepted Answer · 2021-01-22 16:08:29Z

1

Since the positioning of each substring can vary and there are more occurences of the word "Network:", this may be a nice task for FILTERXML():

=MID(FILTERXML("<t><s>"&SUBSTITUTE(A1,"; ","</s><s>")&"</s></t>","//s[starts-with(.,'Network:')]"),10,LEN(A1))

answered Jan 22, 2021 at 16:08

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

1 Comment

Miniedmo Over a year ago

Excellent, this works a treat! Really appreciate the help!

Collectives™ on Stack Overflow

Return text string in excel between a specified word and character

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