Obtain number from web link

**arlu1201** · 10-24-2011, 02:54 PM

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.

**Paul** · 10-24-2011, 04:30 PM

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!

**arlu1201** · 10-25-2011, 02:27 AM

Thank you so much Paul. This formula works perfectly with the 4 digit number. What if i have 6 digit numbers? Its giving me a #Value error for a 6 digit number.

**arlu1201** · 10-26-2011, 03:36 AM

Can anyone help me here?

**arlu1201** · 10-27-2011, 05:31 AM

Bump No Response

**arlu1201** · 10-31-2011, 09:09 AM

Can someone please help me here? I need to extract the 6 digit number from the links i have.

**Paul** · 10-31-2011, 01:44 PM

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.

**arlu1201** · 10-31-2011, 02:39 PM

Thanks Paul, i got it to work with some minor tweaking. Actually, i should have included this part in my sample. Some of the links had "-" in between the words which was creating a problem. I changed it as below -
=MID(SUBSTITUTE(B2,"/","^",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))),FIND("^",SUBSTITUTE(B2,"/","^",LEN(B2)-LEN(SUBSTITUTE(B2,"/",""))))+1,6)
and it works perfectly. Thanks for all your help.