Excel 2007 : Obtain number from web link

Hi All,

Need a little help here.

I have data in the following format -

http://www.friends.com/general/hobbies/123456-Andrea Timothy.html
http://www.friends.com/crafts/234344-John Samuel.html

This runs into several rows. How do i obtain just the numbers like 234344 and 123456 in a separate column? Text to columns doesn't work in this case.

Thanks much.

Hi Arlu,

If the number always immediately follows the last "/" and is then followed by a "-", you could use:

=MID(SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))),FIND("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,FIND("-",A1)-FIND("^",SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))-1)

Assumes there are no carats in your text strings (^).

Could also separate the formula into two columns, e.g.

B1: =SUBSTITUTE(A1,"/","^",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))
C1: =MID(B1,FIND("^",B1)+1,FIND("-",B1)-FIND("^",B1)-1)

You could also use an array formula like:

=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$256),1)),0),COUNT(1*MID(A1,ROW($1:$256),1)))

After typing this formula, you must press CTRL+SHIFT+ENTER, not just ENTER. (The 256 in the formula can be adjusted if your text strings are longer than 256 characters.)

Hope that helps!

Arlu,

I tested the first formula I provided on your text strings and it worked just fine. I then tested it on numbers from 1 to 16 digits in length and had no problems either. Also, the last formula (array formula using CTRL+SHIFT+ENTER) also worked for numbers of varying digits.

Perhaps your cells are formatted to only show so many digits or some odd configuration. Try my formula in a new worksheet with your test URL's. If you can't get it to work, post a copy of your workbook for review.