Forum Discussion
Extract a string of variable length and position from a cell
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
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 Replies
Hi Mark,
That could be
=MID(A1,SEARCH("BBP",A1),SEARCH(":",A1,SEARCH(":",A1,SEARCH("BBP",A1)+1)+1)-SEARCH("BBP",A1))and attached
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
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.
