Home

Extract a string of variable length and position from a cell

%3CLINGO-SUB%20id%3D%22lingo-sub-280627%22%20slang%3D%22en-US%22%3EExtract%20a%20string%20of%20variable%20length%20and%20position%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280627%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EPlease%20could%20you%20advise%20the%20easiest%20way%20to%20extract%20a%20serial%20number%20consisting%20of%20two%20letters%20and%20four%20numbers%20(CM****)%20from%20a%20string%20of%20text.%26nbsp%3B%20Unfortunately%20the%20position%20of%20the%20serial%20number%20is%20not%20always%20in%20the%20same%20space%20of%20the%20string%20of%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20attached%20file%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECatherine%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-280627%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-280660%22%20slang%3D%22en-US%22%3ERe%3A%20Extract%20a%20string%20of%20variable%20length%20and%20position%20from%20a%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-280660%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Catherine%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20try%20this%20formula%20in%20cell%20H6%20and%20drag%20it%20down%3A%3C%2FP%3E%3CPRE%3E%3DMID(C6%2CSEARCH(%22%20CM%22%2CC6)%2B1%2C6)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20SEARCH%20function%20will%20find%20the%20right%20position%20to%20start%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Catherine Petty
New Contributor

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

1 Reply

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies