Extract a string of variable length and position from a cell

[Catherine Petty]

Hi

Please could you advise the easiest way to extract a serial number consisting of two letters and four numbers (CM****) from a string of text.  Unfortunately the position of the serial number is not always in the same space of the string of text.

See attached file 

Thank you

Catherine

[#2]

Hi Catherine,

Please try this formula in cell H6 and drag it down:

=MID(C6,SEARCH(" CM",C6)+1,6)

The SEARCH function will find the right position to start from.

Hope that helps