Hyperlink values
 

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

You are very welcome!
--
Gary''s Student - gsnu200807


"S. Malik" wrote:

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

Thank you Gary's student! Worked like a charm. Is ok to leave function in
workbook or is it better to remove?
--
Mary-Anne in Durban


"S. Malik" wrote:

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

I have to admit that I am clueless about macros. However, I followed all
your steps and the function/macro did exactly what I wanted. However..... I
want to remove the function/macro from the worksheet and my computer as it is
slowing the document down something terrible. I followed your steps and
deleted the function in VBE but everytime I open the document the question is
asked if i want to enable the macro. Help! Where is it lurking? How do I
fell the damn beast. It is no longer useful to me
--
Mary-Anne in Durban


"Mary-Anne" wrote:

Thank you Gary's student! Worked like a charm. Is ok to leave function in
workbook or is it better to remove?
--
Mary-Anne in Durban


"S. Malik" wrote:

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

OK, Im stumped. I also admit to being absolutely clueless about
macros/functions. However, I followed your steps and it worked like a charm.
Not so trying to dispose of said function/macro. It is slowing my document
down terribly and every time I open the document theres the question again:
Do you want to enable macro? It is obviously lurking somewhere in my
document €¦ on my computer €¦ Where?!!!! How do I find the beast and dispose
of it now that I no longer have a use for it?
--
Mary-Anne in Durban


"Mary-Anne" wrote:

Thank you Gary's student! Worked like a charm. Is ok to leave function in
workbook or is it better to remove?
--
Mary-Anne in Durban


"S. Malik" wrote:

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

Hyperlink values
 

Debra Dalgleish shares how to remove the remnants of macros:
http://contextures.com/xlfaqMac.html#NoMacros

Remember to convert those formulas (=hyp()) to values first, or you'll break
them.


Mary-Anne wrote:

I have to admit that I am clueless about macros. However, I followed all
your steps and the function/macro did exactly what I wanted. However..... I
want to remove the function/macro from the worksheet and my computer as it is
slowing the document down something terrible. I followed your steps and
deleted the function in VBE but everytime I open the document the question is
asked if i want to enable the macro. Help! Where is it lurking? How do I
fell the damn beast. It is no longer useful to me
--
Mary-Anne in Durban

"Mary-Anne" wrote:

Thank you Gary's student! Worked like a charm. Is ok to leave function in
workbook or is it better to remove?
--
Mary-Anne in Durban


"S. Malik" wrote:

This was really very helpful Gary. Thanks very much. : )


"Gary''s Student" wrote:

We will use cols A, B, and C. With the hyperlink in col A, in B1 enter"
=A1 this gets the "friendly name"

In C1 enter:

=hyp(A1)

You must also install the following User Defined Function (UDF) to avoid the
#NAME error:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count 0 Then
hyp = r.Hyperlinks(1).Address
Exit Function
End If
If r.HasFormula Then
rf = r.Formula
dq = Chr(34)
If InStr(rf, dq) = 0 Then
Else
hyp = Split(r.Formula, dq)(1)
End If
End If
End Function

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from the Excel worksheet:

=hyp(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

or

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx
for specifics on UDFs

--
Gary''s Student - gsnu200807


"S. Malik" wrote:

Hi,

I have got a column with certain values with each value pointing to a web
address. Now what I need done is, get the values in one columns and links in
other colums. For e.g: In A1, I have "Ben's Kitchen" hyperlinked to
"www.example.com", I want A1= Ben's Kitchen; B1=www.example.com

Any ideas or suggestions?

Thanks very much in advance.

S. Malik

--

Dave Peterson