Is there a better way to extract first word from cell after specific text

[Tooley]

Hi All,

I appreciate there are several posts similar to this, but I can't find one specific to my needs, which is to extract the first word after specific text.

The position of the specific text can change both in it's first character and its word number, but the text I need is always the first word after the specific text. The first word needed can also vary in length.

If I have the following text in cell A1: "[Area: 1] [Unit: C1234 Unit Address] [Week: W:33 P:7 Y:2017]"

and the specific text is: "Unit:"

I can extract the first word ("C1234") using the following formula :

Please Login or Register  to view this content.

I'm always looking to improve, so as this uses the SEARCH function 4 times I was just wandering if there was a better/more efficient way still using a single formula?

Thanks

Tooley

[tim201110]

if such a string
=MID(A1,FIND(":",A1,7)+2,5)