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
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
Highlighted

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
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies