copy url link 1

[PerryG]

I have an Excel 2K spreadsheet that has a value in a cell that is a hyperlink to a website...example: a1=001 and the link is to google.com.

Can I copy just the url link to b2 without taking the visible value of the cell too, i.e. b2=

http://www.google.com?

Thanks.

 

[Wray69]

Right click the cell and go to hyperlink/copy hyperlink, then got to the cell you want to paste it in and it will paste

http://www.google.com

Hope this helps.

Cheers,

Wray

 

[PerryG]

Unless I'm doing it wrong, that copies both the reference (001) and the url link....just a complete copy of the original cell.

Thanks anyway.

 

[Wray69]

If you are going to Hyperlink and then copy hyperlink it should only be copying the link and not the data. So just to verify you are Right Clicking the cell, and going down to HYPERLINK then over to Copy Hyperlink. And then going to the cell where we want the hyperlink and pasting it. This does work for me, I get the hyperlink and not the 001.

Regards,

Wray

 

[cariengon]

Is there a way to accomplish this in bulk? I have a whole list of hyperlinks that I need to copy this way (because I need a number that is in the middle...). I tried to create a macro, but all the macro did was create code for a straight copy/paste. The VBA help file didn't have anything that could accomplish this either.

Thanks,
Carie

 

[Dreamboat]

What do you mean you "need a number that is in the middle"?

Sounds like you're doing something the hard way. Why don't you tell us the exact end result you need and give two examples...just to be sure.



Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[cariengon]

The exact result that I need is the custid that is embedded in the URL of the hyperlink. What I'm doing now is manually doing the right click/copy hyperlink from Column A and pasting the URL into Column B. I then have a MID function in Column C that I'm using to pull out just the custid from the URL.

I have over 200 rows of these hyperlinks and I can't find a way to copy the URL into column B by selecting all of the data in Column A. I'm having to do it cell by cell. I was hoping there was VBA code that could accomplish this, but when I created a macro, it just did a plain copy/paste - which put in the hyperlink and not the URL.

Here's an example:

Column A - Hyperlink: Anoka County
Column B - URL pasted by right click/copy Hyperlink from Column A:

http://jwiapps.jeffersonwells.com/ClientMap/default.asp?custid=1006742&top=2&left=1

Column C: =MID(B2,64,7) - returns 1006742

Is there an easier way???

Thanks,
Carie

 

[Dreamboat]

Is it always the same URL except for the number?
Copy your cells.
Paste into Word.
Hit Alt+F9
Do a find for the strings you want DELETED.
In the replace with box, don't put anything.
Hit replace all.
You might have to do a couple of find/replaces before you get them all out of there.

Or, at least you can get rid of the hyperlink property by selecting all in Word and choose NORMAL as the style, which should get rid of the hyperlink.
Paste back into excel and do a Data-->text-to-columns thing

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[cariengon]

Very cool!! Thank you!

 

[Dreamboat]

Sure.

Sometimes, the manual method is just a lot quicker.
LOL

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[KenWright]

Assuming your data in Col A1:A100, and that they all have the same length url prior to the number, then in B1 put the following formula and copy down:-

=MID(TEXT(A2,"#&quot,64,7)

the former will give you text, but if you actually want it as a number then just amend the formula as follows:-

=--MID(TEXT(A2,"#&quot,64,7)

Regards
Ken.................

 

[Dreamboat]

No, Ken! You cannot come in now!! He has already given ME a star!!!

LOL
LOL

(hee hee)

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[KenWright]

rotflmao

Perry - I take it all back - Do NOT NOT NOT NOT read my note, coz I cannot have Anne get upset with me ( She's far too nice!! :-> )

Regards
Ken.............

 

[Dreamboat]

LOL
Silly boy. I NEVER get upset...

Anne Troy
Way cool stuff:

http://www.cafeshops.com/OfficeExperts

 

[KenWright]

'boy' = young!!!

Hugs and kisses flying your way.

Rgds
Ken...........