Extract 7 digit code from a text string

[markvdhouten]

Hi All,

Could anyone help with this?

FMAKERS PANCAKES MILKTART 5X40 1'S (6009693012936) (2490281) (2)

I need to extract the branch code 2490281 from the above text string in a cell. The full text string can vary in length & the extracted number can also vary, but is always 7 digits long and could be anywhere in the string.

Any suggestions would be appreciated.

[JohnTopley]

Please see yellow banner at top of page of how to attach your workbook. Ensure your sample is representative of the variability of your data.

[Pete_UK]

If that string is in A2 and is representative of the rest of your data, then you can use this formula in B2:

=MID(A2,FIND("|",SUBSTITUTE(A2,") (","|",1))+3,7)

then copy down as required.

Hope this helps.

Pete

[markvdhouten]

Hi Pete,

Thanks for taking the time to answer me. Not quite sure of the formulation. I did try and change the string to:

FMAKERS PANCAKES (2490281) MILKTART 5X40 1'S (6009693012936) (2), by moving the 7 digit number elsewhere in the string, but no success in extracting with your formula

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

In the sample workbook, include examples that cover all possible types of text string and add a column manually showing what you want to extract.

[KOKOSEK]

Try:

Please Login or Register  to view this content.

	A	B
1	FMAKERS PANCAKES MILKTART 5X40 1'S (6009693012936) (2490281) (2)	2490281
2	FMAKERS PANCAKES (2490281) MILKTART 5X40 1'S (6009693012936) (2)	2490281

	A	B
1	FMAKERS PANCAKES MILKTART 5X40 1'S (6009693012936) (2490281) (2)	=MID(A1,SEARCH("(???????)",A1)+1,7)
2	FMAKERS PANCAKES (2490281) MILKTART 5X40 1'S (6009693012936) (2)	=MID(A2,SEARCH("(???????)",A2)+1,7)

[markvdhouten]

Hi All,

I have attached a sample spreadsheet per rules.

[AliGW]

See Kokosek's solution above.

This will make it present as a number rather than text:

=--MID(A4,SEARCH("(???????)",A4)+1,7)

[markvdhouten]

HI Kokosek,

Perfect, seems to work every time.

Many thanks

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.