Extract a string of variable length and position from a cell

[Mark Lee]

I have a list of data from which i need to extract a part of

GPF:E0690:BBP:L28211:OHS:4573:

BBP:L58:

BBP:L60436:OHS:10097:

OHS:2636:GPF:E1172:BBP:L27332:

From this list i need to extract the highlighted sections. i have tried combinations of MID, FIND, SEARCH, LEFT and RIGHT but just cant put together the right formula to get the data i need.

Any help would be greatly appreciated

Mark

[#2 · Accepted solution]

Hi Mark,

That could be

=MID(A1,SEARCH("BBP",A1),SEARCH(":",A1,SEARCH(":",A1,SEARCH("BBP",A1)+1)+1)-SEARCH("BBP",A1))

and attached

[#3]

Thats great and worked perfectly.

would you be able to outline each part so that i can fully understand it in order to be able to replicate it in other situations

Thanks

Mark

[#4]

Mark, in attached file I split the formula on components. You may start from one in column G and replace one by one references on cells in columns from C to F by formulas in that cells - as result it will be the final formula.