Hi,

I have downloaded a huge list of rock artist names together with their
musical styles into an MS Excel 2003 worksheet. Each name has a hyperlink
attached to it, and clicking the hyperlink will open a web page with
detailed information about this artist. For database purposes, the hyperlink
is extremely useful for identifying the artist, as each hyperlink contains a
number that is unique for that artist. So getting the URL value of the
hyperlink and extracting this number from it will give me the means to
differentiate between artists with identical names rather than to confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel open the Edit Hyperlink window copy the
hyperlink-address close the Edit Hyperlink window paste into the cell.
This will fully display the URL value of the hyperlink. But this is not
practical for my situation, where I'd have to repeat this for about 50,000
times. Using the Excel Macro Recorder I have tried to create a macro that
will copy and paste the hyperlink into the cell to the right of the cell
with the original artist data, using the sequence of the manual procedure.
The macro fails, however, as the macro-recorder doesn't record the action of
copying the hyperlink-address during the opening of the Edit Hyperlink
window; so it will paste any content that happens to be on the Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me find a way
to solve this little problem.

Ron.

Hi
could you give some examples for your hyperlinks. That is if you want
to extract this number at which position is this number stored?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

I have downloaded a huge list of rock artist names together with
their
musical styles into an MS Excel 2003 worksheet. Each name has a
hyperlink
attached to it, and clicking the hyperlink will open a web page with
detailed information about this artist. For database purposes, the
hyperlink
is extremely useful for identifying the artist, as each hyperlink
contains a
number that is unique for that artist. So getting the URL value of
the
hyperlink and extracting this number from it will give me the means
to
differentiate between artists with identical names rather than to
confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel open the Edit Hyperlink window copy the
hyperlink-address close the Edit Hyperlink window paste into the
cell.
This will fully display the URL value of the hyperlink. But this is
not
practical for my situation, where I'd have to repeat this for about
50,000
times. Using the Excel Macro Recorder I have tried to create a macro
that
will copy and paste the hyperlink into the cell to the right of the
cell
with the original artist data, using the sequence of the manual
procedure.
The macro fails, however, as the macro-recorder doesn't record the
action of
copying the hyperlink-address during the opening of the Edit
Hyperlink
window; so it will paste any content that happens to be on the
Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me find
a way
to solve this little problem.

Ron.

Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
Hi
could you give some examples for your hyperlinks. That is if you want
to extract this number at which position is this number stored?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

I have downloaded a huge list of rock artist names together with
their
musical styles into an MS Excel 2003 worksheet. Each name has a
hyperlink
attached to it, and clicking the hyperlink will open a web page with
detailed information about this artist. For database purposes, the
hyperlink
is extremely useful for identifying the artist, as each hyperlink
contains a
number that is unique for that artist. So getting the URL value of
the
hyperlink and extracting this number from it will give me the means
to
differentiate between artists with identical names rather than to
confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel open the Edit Hyperlink window copy the
hyperlink-address close the Edit Hyperlink window paste into the
cell.
This will fully display the URL value of the hyperlink. But this is
not
practical for my situation, where I'd have to repeat this for about
50,000
times. Using the Excel Macro Recorder I have tried to create a macro
that
will copy and paste the hyperlink into the cell to the right of the
cell
with the original artist data, using the sequence of the manual
procedure.
The macro fails, however, as the macro-recorder doesn't record the
action of
copying the hyperlink-address during the opening of the Edit
Hyperlink
window; so it will paste any content that happens to be on the
Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me find
a way
to solve this little problem.

Ron.

Hi
try in an adjacent cell
=MID(A1,FIND("=")+1,20)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the
number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
Hi
could you give some examples for your hyperlinks. That is if you
want
to extract this number at which position is this number stored?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

I have downloaded a huge list of rock artist names together with
their
musical styles into an MS Excel 2003 worksheet. Each name has a
hyperlink
attached to it, and clicking the hyperlink will open a web page
with
detailed information about this artist. For database purposes,
the
hyperlink
is extremely useful for identifying the artist, as each hyperlink
contains a
number that is unique for that artist. So getting the URL value
of
the
hyperlink and extracting this number from it will give me the
means
to
differentiate between artists with identical names rather than to
confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel open the Edit Hyperlink window copy
the
hyperlink-address close the Edit Hyperlink window paste into
the
cell.
This will fully display the URL value of the hyperlink. But this
is
not
practical for my situation, where I'd have to repeat this for
about
50,000
times. Using the Excel Macro Recorder I have tried to create a
macro
that
will copy and paste the hyperlink into the cell to the right of
the
cell
with the original artist data, using the sequence of the manual
procedure.
The macro fails, however, as the macro-recorder doesn't record
the
action of
copying the hyperlink-address during the opening of the Edit
Hyperlink
window; so it will paste any content that happens to be on the
Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me
find
a way
to solve this little problem.

Ron.

Hi Frank,

Perhaps I haven't stated the problem as clearly as I should have done. The
artist name is the only text that is visible in the cell. The hyperlink is
attached to the artist name, but invisible. It becomes only visible when
placing the mouse pointer over the cell, or when opening the Edit Hyperlink
window. Spreadsheet functions don't seem to be able to manipulate
hyperlinks, as the hyperlink isn't really a part of the cell contents. The
hyperlink seems to behave more like an attachment to the cell contents. The
only way to get a grip on the hyperlink code seems to be by using the Edit
Hyperlink window, which doesn't feature something like a built-in "copy
hyperlink as text" function. So the real problem is how to convert the
hyperlink to "normal text". After this step has been taken, the usual text
functions can be applied.

To give some real examples:

The only thing visible in a cell is the artist name, so this is how the
contents of a cell look like:

Fifty Foot Hose


This is the hyperlink that is attached to this name:

http://www.mmguide.musicmatch.com/ar...RTISTID=783780

Remember that this is not "normal text" but hypertext.

There is also a menu option in Excel: Edit Paste as Hyperlink, but this
option doesn't seem to do anything special as compared to the normal Paste
option. Applied to my artist data it only copies the cell contents and
doesn't convert the hypertext to normal text.

I somehow get the feeling that perhaps the Edit Hyperlink window should be
extended with a "copy hyperlink as text" function. Is such a thing possible
in Microsoft Excel 2003?

Regards,
Ron
Groningen, The Netherlands



"Frank Kabel" schreef in bericht
...
Hi
try in an adjacent cell
=MID(A1,FIND("=")+1,20)

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

An example of the hyperlink:
http://www.mmguide.musicmatch.com/ar...RTISTID=862413

The number starts at position 62. The number of digits by which the
number
is composed varies

Regards,
Ron
Groningen, The Netherlands


"Frank Kabel" schreef in bericht
...
Hi
could you give some examples for your hyperlinks. That is if you
want
to extract this number at which position is this number stored?

--
Regards
Frank Kabel
Frankfurt, Germany

"Ron van Oijen" schrieb im Newsbeitrag
i.nl...
Hi,

I have downloaded a huge list of rock artist names together with
their
musical styles into an MS Excel 2003 worksheet. Each name has a
hyperlink
attached to it, and clicking the hyperlink will open a web page
with
detailed information about this artist. For database purposes,
the
hyperlink
is extremely useful for identifying the artist, as each hyperlink
contains a
number that is unique for that artist. So getting the URL value
of
the
hyperlink and extracting this number from it will give me the
means
to
differentiate between artists with identical names rather than to
confuse
them. It is easy to manually copy the hyperlinks one-by-one by
right-clicking the cel open the Edit Hyperlink window copy
the
hyperlink-address close the Edit Hyperlink window paste into
the
cell.
This will fully display the URL value of the hyperlink. But this
is
not
practical for my situation, where I'd have to repeat this for
about
50,000
times. Using the Excel Macro Recorder I have tried to create a
macro
that
will copy and paste the hyperlink into the cell to the right of
the
cell
with the original artist data, using the sequence of the manual
procedure.
The macro fails, however, as the macro-recorder doesn't record
the
action of
copying the hyperlink-address during the opening of the Edit
Hyperlink
window; so it will paste any content that happens to be on the
Windows
Clipboard at the time of pasting.

I would very much appreciate any suggestions that would help me
find
a way
to solve this little problem.

Ron.

Hi Ron,

Function HyperlinkAddress(cell)
On Error Resume Next
HyperlinkAddress = cell.Hyperlinks(1).Address
if hyperlinkaddress = 0 then hyperlinkaddress = ""
End Function

usage:
=HyperlinkAddress(A2)
=personal.xls!hyperlinkaddress('links sheet'!A2)


so you might use continuing from Frank's example, something like

=MID(personal.xls!hyperlinkaddress(A2),FIND("=")+1 ,20)
or
=IF(personal.xls!hyperlinkaddress(A2)="", "", personal.xls!hyperlinkaddress('links sheet'!A2))



More information on Hyperlinks on
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Ron van Oijen" wrote ...
Perhaps I haven't stated the problem as clearly as I should have done. The
artist name is the only text that is visible in the cell. The hyperlink is
attached to the artist name, but invisible. It becomes only visible when