extract number from a string of text - Excel

问题:

I'm trying to pull a number out of the middle of a string of text in excel. the string is a mixtire of alpha, numeric and some characters.

Example data string:-

Web Address 
/products_list.php?retailer=8&cat=43
/products_list.php?retailer=22&cat=43
/products_list.php?retailer=8&cat=1011
/products_list.php?retailer=81&cat=1023
/products_list.php?retailer=147&cat=1224
/products_list.php?retailer=8&cat=1
/products_list.php?retailer=147&cat=4
/products_list.php?retailer=147&cat=401

What i want to achieve is

I got as far as pulling out all the numbers from the text but really couldn't work out how to just get the numbers in the middle or the ones at the end.

My attempted code to fix the issue was:-

=SUM(MID(0&A3,LARGE(ISNUMBER(--MID(A3,ROW(INDIRECT("1:"&LEN(A3))),1))*ROW(INDIRECT("1:"&LEN(A3))),ROW(INDIRECT("1:"&LEN(A3))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A3)))/10)

SCE for an array formula.

If possible, I would prefer a formula over a macro.

oh in case it makes a difference im using Excel 2010.

回答1:

Retailer (B2):

=LEFT(RIGHT(A2,LEN(A2)-FIND("=",A2)),FIND("&",RIGHT(A2,LEN(A2)-FIND("=",A2)))-1)

Cat Number (C2):

=RIGHT(A2,LEN(A2)-(FIND("cat=",A2)+3))

drag the formula down and

回答2:

This looks really ugly (I am no excel expert), but it worked with the list you provided.

Formula for retailer:

=MID(A2,FIND("retailer=",A2)+9,FIND("&",A2)-(FIND("retailer=",A2)+9))

Formula for cat number:

=MID(A2,FIND("cat=",A2)+4,100)