convert hyperlink to email address

[Seemore]

I have an entire column of email addresses which are in a hyperlink
format. I want to convert the hyperlink to the actual email address
text. How can I do that?

 

[Nigel]

Columns("A:A").Hyperlinks.Delete

 

[Seemore]

Columns("A:A").Hyperlinks.Delete

--

Regards,
Nigel
(e-mail address removed)






- Show quoted text -

I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?

 

[Conan Kelly]

Seemore,

1. Make sure the file you want to change is active (you are working in it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on the
same line as the code before you press enter).

Hopefully that will accomplish what you are trying to do.

HTH,

Conan

Columns("A:A").Hyperlinks.Delete

--

Regards,
Nigel
(e-mail address removed)






- Show quoted text -

I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?

 

[Seemore]

Seemore,

1.  Make sure the file you want to change is active (you are working in it).
2.  Make sure the sheet that has the hyperlinks is active.
3.  Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4.  If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE).
5.  Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on the
same line as the code before you press enter).

Hopefully that will accomplish what you are trying to do.

HTH,

Conan

Columns("A:A").Hyperlinks.Delete

- Show quoted text -

I appreciate the help.  I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete.  where do I type this in?- Hide quotedtext -

- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.

 

[Conan Kelly]

Seemore,

Any command you enter in the Immediate window of the VBE will affect what
ever file(s) are open in XL. After you hit the Enter key while the cursor
is on that line of code, flip back to XL to see if made any changes. There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line of
code into the immediate window:

Columns("A:A").Hyperlinks.Delete

If the flashing cursor is not on the same line as the "...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,

1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on the
same line as the code before you press enter).

Hopefully that will accomplish what you are trying to do.

HTH,

Conan

Columns("A:A").Hyperlinks.Delete

- Show quoted text -

I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide quoted
text -

- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.

 

[Seemore]

Seemore,

Any command you enter in the Immediate window of the VBE will affect what
ever file(s) are open in XL.  After you hit the Enter key while the cursor
is on that line of code, flip back to XL to see if made any changes.  There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one you
want to delete all of the hyperlinks from).  Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line of
code into the immediate window:

    Columns("A:A").Hyperlinks.Delete

If the flashing cursor is not on the same line as the "...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the same
line.  When the cursor is on the same line, hit Enter.  The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of the
active sheet in the active workbook) in XL.  Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,

1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on the
same line as the code before you press enter).

Hopefully that will accomplish what you are trying to do.


"Seemore" <[email protected]> wrote in message

I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide quoted
text -

- Show quoted text -

I appreciate the help.  Still having problems though.  I got the code
into the immediate box and hit enter but nothing seemed to happen.  Is
the window supposed to close or somehow get saved?  Also, how do I run
this?  As a novice I really appreciate your help.- Hide quoted text -

- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed the hyperlink to text,
however, I was trying extract the actual address from the hyperlink.
example, hyperlink "bob_james" covert to (e-mail address removed)

 

[Conan Kelly]

Seemore,

I have a solution but it is a little more complicated than the previous one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you want
to convert to email addresses.

Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects). There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,

Any command you enter in the Immediate window of the VBE will affect what
ever file(s) are open in XL. After you hit the Enter key while the cursor
is on that line of code, flip back to XL to see if made any changes. There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line of
code into the immediate window:

Columns("A:A").Hyperlinks.Delete

If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,

1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).

Hopefully that will accomplish what you are trying to do.


"Seemore" <[email protected]> wrote in message

I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide quoted
text -

- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -

- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed the hyperlink to text,
however, I was trying extract the actual address from the hyperlink.
example, hyperlink "bob_james" covert to (e-mail address removed)

 

[Seemore]

Seemore,

I have a solution but it is a little more complicated than the previous one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you want
to convert to email addresses.

Then:
1.  Go to the VBE ([Alt] + [F11])
2.  If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3.  In project explorer, there might be several items listed, but you should
see something like:
    VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4.  If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).  There
may be a couple others as well (Modules, Forms, etc...).
5.  Right-Click the project or any folder/item in the project.
6.  Click Insert
7.  Click Module
8.  A blank window should open up to the right.  If not, double-click the
newly added module.
9.  Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
    Dim prngCell As Range

    For Each prngCell In Selection
        prngCell = prngCell.Hyperlinks(1).Address
        prngCell.Hyperlinks.Delete
        prngCell = Replace(prngCell, "mailto:", "")

    Next prngCell
End Sub

10.  Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11.  Flip back to XL to verify that it worked.  If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work.  This code will loop
through each cell that is selected, extract the email address and remove the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,

Any command you enter in the Immediate window of the VBE will affect what
ever file(s) are open in XL. After you hit the Enter key while the cursor
is on that line of code, flip back to XL to see if made any changes. There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line of
code into the immediate window:

If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,
1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE)..
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in ahyperlink
format. I want to convert thehyperlinkto the actual email address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide quoted
text -
- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range


For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")


Next prngCell
End Sub

- Show quoted text -

I got it now, your instructions really helped.  It worked, however,
not the way I intended.  The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james"  covert to (e-mail address removed)- Hide quoted text -

- Show quoted text -

I swear I was close. I think I followed everything but wound up with
an error message. Runtime error 9
Subscript out of range. I appreciate your patience.

 

[Seemore]

Seemore,

I have a solution but it is a little more complicated than the previous one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you want
to convert to email addresses.

Then:
1.  Go to the VBE ([Alt] + [F11])
2.  If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3.  In project explorer, there might be several items listed, but you should
see something like:
    VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4.  If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).  There
may be a couple others as well (Modules, Forms, etc...).
5.  Right-Click the project or any folder/item in the project.
6.  Click Insert
7.  Click Module
8.  A blank window should open up to the right.  If not, double-click the
newly added module.
9.  Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
    Dim prngCell As Range

    For Each prngCell In Selection
        prngCell = prngCell.Hyperlinks(1).Address
        prngCell.Hyperlinks.Delete
        prngCell = Replace(prngCell, "mailto:", "")

    Next prngCell
End Sub

10.  Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11.  Flip back to XL to verify that it worked.  If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work.  This code will loop
through each cell that is selected, extract the email address and remove the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,

Any command you enter in the Immediate window of the VBE will affect what
ever file(s) are open in XL. After you hit the Enter key while the cursor
is on that line of code, flip back to XL to see if made any changes. There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line of
code into the immediate window:

If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,
1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination to
open the Immediate window (usually it spans the lower part of the VBE)..
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in ahyperlink
format. I want to convert thehyperlinkto the actual email address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide quoted
text -
- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -

- Show quoted text -

I got it now, your instructions really helped.  It worked, however,
not the way I intended.  The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james"  covert to (e-mail address removed)- Hide quoted text -

- Show quoted text -

I got a runtime error 9, subscript out of range. Not sure where I
went wrong.

 

[Conan Kelly]

Seemore,

I'm guessing that one of the cells in the selection does not have a
hyperlink.

Flip back to XL and see if some of the cells were changed properly.

I adjusted the code a little bit to account for cells that do not have
hyperlinks. Select all of the code that I gave you before ([Ctrl] + A) and
delete.

then paste the following code in its place and try to run it (from "Option
Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
If prngCell.Hyperlinks.Count > 0 Then
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")
End If

Next prngCell
End Sub



Let me know if this works.

Conan

PS. I'm going home now...it's late (9:15 pm). If you have any other
problems, hopefully there is someone else monitoring this newgroup that can
help you out. If not, I will be glat to help you out tomorrow.

Seemore,

I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.

Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,

Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line
of
code into the immediate window:

If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the
same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of
the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,
1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft
Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination
to
open the Immediate window (usually it spans the lower part of the
VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in ahyperlink
format. I want to convert thehyperlinkto the actual email address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide
quoted
text -
- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range


For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")


Next prngCell
End Sub

- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james" covert to (e-mail address removed)- Hide quoted
text -

- Show quoted text -

I swear I was close. I think I followed everything but wound up with
an error message. Runtime error 9
Subscript out of range. I appreciate your patience.

 

[Conan Kelly]

See my response to your last post....

Seemore,

I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.

Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,

Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.

That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.

To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.

If your email addresses are in column A, then copy and paste this line
of
code into the immediate window:

If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the
same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of
the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again).

Please write back if you are still having problems.

HTH,

Conan

Seemore,
1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft
Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination
to
open the Immediate window (usually it spans the lower part of the
VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in ahyperlink
format. I want to convert thehyperlinkto the actual email address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide
quoted
text -
- Show quoted text -

I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -

- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james" covert to (e-mail address removed)- Hide quoted
text -

- Show quoted text -

I got a runtime error 9, subscript out of range. Not sure where I
went wrong.

 

[Seemore]

See my response to your last post....

Seemore,

I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.

Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,
Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the next
line) in the VBE when you do this.
That code should delete all of the hyperlinks in column A of the active
worksheet of the active workbook.
To make things easier, make sure there is only one XL file open (the one
you
want to delete all of the hyperlinks from). Then make sure the sheet you
want to delete hperlinks from is active.
If your email addresses are in column A, then copy and paste this line
of
code into the immediate window:
Columns("A:A").Hyperlinks.Delete
If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the
same
line. When the cursor is on the same line, hit Enter. The cursor should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of
the
active sheet in the active workbook) in XL. Flip back to XL to make sure
(you can close the VBE if you want...not likely you will use it again)..
Please write back if you are still having problems.
HTH,
Conan
On Jan 29, 8:57 pm, "Conan Kelly"
Seemore,
1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft
Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key combination
to
open the Immediate window (usually it spans the lower part of the
VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into the
Immediate window and press enter (make sure the blinking cursor is on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in ahyperlink
format. I want to convert thehyperlinkto the actual email address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide
quoted
text -
- Show quoted text -
I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -
- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james" covert to (e-mail address removed)- Hide quoted
text -

- Show quoted text -

I got a runtime error 9, subscript out of range.  Not sure where I
went wrong.- Hide quoted text -

- Show quoted text -

Oh my, it works! That is amazing. Thank you so much. If you don't
mind, one last question. Everytime I create a new worksheet, do I
have to create another vba? for each one or is there a way to save it?

 

[Conan Kelly]

Seemore,

...or is there a way to save it?

This code becomes part of the workbook/file you created the module in. When
you close and save the workbook, it is saved.

.... Everytime I create a new worksheet, do I
have to create another vba? for each one ...

That depends on what you mean by "a new worksheet".

Let me go over my lingo for XL (whether or not it is the correct lingo, that
is another matter). I use the terms workbook and file interchangeably. For
me, an Excel file is a workbook, a workbook is an Excel file. A
workbook/file contains worksheets. Many people refer to worksheets as
"tabs" because each worksheet does have a tab in the lower left corner of
the XL window. The tab has the worksheet's name on it (and you can change
its name by double-clicking the tab) and you can change its color (If I
could, I would try to get everyone to refer to worksheets as "worksheets"
and quit using "tabs"). And those people, who usually call sheets "tabs",
will sometimes call workbooks/files "worksheets".

If you mean creating a new worksheet in the workbook/file we added this code
to when you say "create a new worksheet", then no. The code is part of the
file and can be used on any sheet in the file.

If you mean creating a new workbook when you say "create a new worksheet",
then not necessarily, but I might help out alot if you were to move that
code somewhere else. Let me explain:

I designed this code to work on the active sheet of the active book. So,
you could have 3 different files open and use this code on any sheet in any
of the files (you are not limited to using this code in the file where it is
stored), BUT the file where the code is saved needs to be open. So, if you
want to use this code in another workbook, this workbook where you stored it
needs to be open.

If that is undesirable, then we can move this code to your "PERSONAL.XLS"
file. The personal.xls file is a file that is usually opened automatically
when XL starts and is hidden so many people don't even know it is there. It
is meant to put code that is meant to be used on any workbook or to do other
things not even related to workbooks.

If you want to go this route, then follow my instructions, in one of my
earlier posts, for creating a new module in the VBAProject for your file
using the Project Explorer, but instead of creating this new module in
"VBAProject (Your File Name.xls)", create it in "VBAProject (PERSONAL.XLS)".
Then you can copy this code into this new module in the personal.xls
vbaproject. If you don't see "VBAProject (PERSONAL.XLS)" in Project
Explorer, then it might not exist yet (I'm not quite sure if it exist by
default or if it is automatically created the first time we record a macro
to it). We can try to create it by recording a macro to it:
1. Flip back to XL
2. Tools menu > Macro > Record New Macro... (hopefully you are not using XL
2007...if you are, I can't help you too much, but the process will be
somewhat similar)
3. In the "Record Macro" dialog box, the only thing I would change is the
"Store macro in:" dropdown to "Personal Macro Workbook", if it is not
already there. Just leave the default setting for everything else.
4. Click the OK button.
5. A "Stop Recording" toolbar should appear somewhere. Mine only has 2
buttons on it: a Stop Recording button (just a blue square) and a Relative
Reference button and I can only see the first 2 letters and part of the 3rd
of the word "Stop Recording" in the title bar of the toolbar.

Now we are in the Recording Macro mode. Everything you do will be recorded
and XL will generate VBA code to reproduce everything you record.

Now make some changes. It can be just as simple as selecting a different
cell, but you can do anything: type text/numbers/formulas into cells,
change formatting, etc... what ever you want. We aren't going to keep this
macro/code, we are just doing this to create the personal.xls file. When
you are done making changes, hit the stop button on the Stop Recording
toolbar.

Now flip back to the VBE and you should see the "VBAProject (PERSONAL.XLS)"
in the Project Explorer and it will have a "Modules" folder in it with and
probably a "Module1" in that folder. You could just paste my code into this
Module1 instead of creating a new module. If the Module1 code window is not
open, double-click "Module1" in the Modules folder of the "VBAProject
(PERSONAL.XLS)". When you open this module, you will see the VBA code XL
created when you were in recording mode. You can delete that code if you
want...probably won't use it again.

Now that you've added code to the personal.xls, it doesn't mean that those
changes are saved. If you are doing something that causes XL to lock up and
you need to force XL to quit, any changes that you made to the personal.xls
will be lost. To manually save the changes, make sure any item in the
"VBAProject (PERSONAL.XLS)" in Project Explorer is selected and click the
Save button on the VBE toolbar (if you hover your mouse over the save
button, a tooltip will pop up saying "Save PERSONAL.XLS (Ctrl+S)"). When
you make changes to the personal.xls and forget to save them, when you go to
close XL, it will remind you that changes have been made and ask you if you
want to save them.

Also, depending on your Security level setting in XL, you may not be able to
run any macros/code that you create. To check the setting, in XL click
Tools menu > Macro > Security... and then I would choose the Medium setting
on the Security Level tab.

Now, every time you open a file with macros/code in it, you will be asked to
enable/disable macros in a Security Warning. If you ever receive a file
from someone you don't know/trust, you should disable the macros. They
potentially could cause damage.

Now that this code is save in personal.xls, it will be available for you to
use whenever you have XL open. And you don't even have to open the VBE to
use it. When you are working on a workbook that has hyperlinks that you
want to change:
1. select the hyperlinks
2. click the Tools menu > Macros > Macros... (or do [Alt] + [F8] key
combination)
3. select either "All Open Workbooks" or "PERSONAL.XLS' from the "Macros
in:" dropdown box
4. select the "PERSONAL.XLS!RemoveLinks" macro from the list box and click
the Run button (or double-click the macro from the list box).

AND BINGO!!! all of the selected email hyperlinks will be replaced with the
email addresses.

HTH,

Conan

See my response to your last post....

Seemore,

I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.

First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.

Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):

Option Explicit

Sub RemoveLinks()
Dim prngCell As Range

For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")

Next prngCell
End Sub

10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a
CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.

As long as you HAVE SELECTED ALL OF THE CELLS that you want to change
the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.

Please write back if you have any problems.

HTH,

Conan

Seemore,
Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the
next
line) in the VBE when you do this.
That code should delete all of the hyperlinks in column A of the
active
worksheet of the active workbook.
To make things easier, make sure there is only one XL file open (the
one
you
want to delete all of the hyperlinks from). Then make sure the sheet
you
want to delete hperlinks from is active.
If your email addresses are in column A, then copy and paste this line
of
code into the immediate window:
Columns("A:A").Hyperlinks.Delete
If the flashing cursor is not on the same line as the
"...Hyperlinks.Delete"
code, then push the arrow up button to make sure the cursor is on the
same
line. When the cursor is on the same line, hit Enter. The cursor
should
move down to the next line and hopfully the hyperlinks will have been
deleted from your email addresses (as long as they are in column A of
the
active sheet in the active workbook) in XL. Flip back to XL to make
sure
(you can close the VBE if you want...not likely you will use it
again).
Please write back if you are still having problems.
HTH,
Conan
On Jan 29, 8:57 pm, "Conan Kelly"
Seemore,
1. Make sure the file you want to change is active (you are working
in
it).
2. Make sure the sheet that has the hyperlinks is active.
3. Press the [Alt] + [F11] key combination to open the Microsoft
Visual
Basic editor (VBE)
4. If it is not already showing, press the [Ctrl] + G key
combination
to
open the Immediate window (usually it spans the lower part of the
VBE).
5. Paste that line of code (Columns("A:A").Hyperlinks.Delete) into
the
Immediate window and press enter (make sure the blinking cursor is
on
the
same line as the code before you press enter).
Hopefully that will accomplish what you are trying to do.
HTH,
Conan
Columns("A:A").Hyperlinks.Delete
--
Regards,
Nigel
(e-mail address removed)

I have an entire column of email addresses which are in
ahyperlink
format. I want to convert thehyperlinkto the actual email
address
text. How can I do that?- Hide quoted text -
- Show quoted text -
I appreciate the help. I am unfortunately a novice when it comes to
this stuff and I don't understand what to do with the
Columns("A:A").Hyperlinks.Delete. where do I type this in?- Hide
quoted
text -
- Show quoted text -
I appreciate the help. Still having problems though. I got the code
into the immediate box and hit enter but nothing seemed to happen. Is
the window supposed to close or somehow get saved? Also, how do I run
this? As a novice I really appreciate your help.- Hide quoted text -
- Show quoted text -

I got it now, your instructions really helped. It worked, however,
not the way I intended. The code changed thehyperlinkto text,
however, I was trying extract the actual address from thehyperlink.
example,hyperlink"bob_james" covert to (e-mail address removed)- Hide quoted
text -

- Show quoted text -

I got a runtime error 9, subscript out of range. Not sure where I
went wrong.- Hide quoted text -

- Show quoted text -

 

[Seemore]

Seemore,

...or is there a way to save it?

This code becomes part of the workbook/file you created the module in.  When
you close and save the workbook, it is saved.

....  Everytime I create a new worksheet, do I
have to create another vba? for each one ...

That depends on what you mean by "a new worksheet".

Let me go over my lingo for XL (whether or not it is the correct lingo, that
is another matter).  I use the terms workbook and file interchangeably.  For
me, an Excel file is a workbook, a workbook is an Excel file.  A
workbook/file contains worksheets.  Many people refer to worksheets as
"tabs" because each worksheet does have a tab in the lower left corner of
the XL window.  The tab has the worksheet's name on it (and you can change
its name by double-clicking the tab) and you can change its color (If I
could, I would try to get everyone to refer to worksheets as "worksheets"
and quit using "tabs").  And those people, who usually call sheets "tabs",
will sometimes call workbooks/files "worksheets".

If you mean creating a new worksheet in the workbook/file we added this code
to when you say "create a new worksheet", then no.  The code is part of the
file and can be used on any sheet in the file.

If you mean creating a new workbook when you say "create a new worksheet",
then not necessarily, but I might help out alot if you were to move that
code somewhere else.  Let me explain:

I designed this code to work on the active sheet of the active book.  So,
you could have 3 different files open and use this code on any sheet in any
of the files (you are not limited to using this code in the file where it is
stored), BUT the file where the code is saved needs to be open.  So, if you
want to use this code in another workbook, this workbook where you stored it
needs to be open.

If that is undesirable, then we can move this code to your "PERSONAL.XLS"
file.  The personal.xls file is a file that is usually opened automatically
when XL starts and is hidden so many people don't even know it is there.  It
is meant to put code that is meant to be used on any workbook or to do other
things not even related to workbooks.

If you want to go this route, then follow my instructions, in one of my
earlier posts, for creating a new module in the VBAProject for your file
using the Project Explorer, but instead of creating this new module in
"VBAProject (Your File Name.xls)", create it in "VBAProject (PERSONAL.XLS)".
Then you can copy this code into this new module in the personal.xls
vbaproject.  If you don't see "VBAProject (PERSONAL.XLS)" in Project
Explorer, then it might not exist yet (I'm not quite sure if it exist by
default or if it is automatically created the first time we record a macro
to it).  We can try to create it by recording a macro to it:
1.  Flip back to XL
2.  Tools menu > Macro > Record New Macro... (hopefully you are not using XL
2007...if you are, I can't help you too much, but the process will be
somewhat similar)
3.  In the "Record Macro" dialog box, the only thing I would change is the
"Store macro in:" dropdown to "Personal Macro Workbook", if it is not
already there.  Just leave the default setting for everything else.
4.  Click the OK button.
5.  A "Stop Recording" toolbar should appear somewhere.  Mine only has2
buttons on it:  a Stop Recording button (just a blue square) and a Relative
Reference button and I can only see the first 2 letters and part of the 3rd
of the word "Stop Recording" in the title bar of the toolbar.

Now we are in the Recording Macro mode.  Everything you do will be recorded
and XL will generate VBA code to reproduce everything you record.

Now make some changes.  It can be just as simple as selecting a different
cell, but you can do anything:  type text/numbers/formulas into cells,
change formatting, etc... what ever you want.  We aren't going to keep this
macro/code, we are just doing this to create the personal.xls file.  When
you are done making changes, hit the stop button on the Stop Recording
toolbar.

Now flip back to the VBE and you should see the "VBAProject (PERSONAL.XLS)"
in the Project Explorer and it will have a "Modules" folder in it with and
probably a "Module1" in that folder.  You could just paste my code into this
Module1 instead of creating a new module.  If the Module1 code window isnot
open, double-click "Module1" in the Modules folder of the "VBAProject
(PERSONAL.XLS)".  When you open this module, you will see the VBA code XL
created when you were in recording mode.  You can delete that code if you
want...probably won't use it again.

Now that you've added code to the personal.xls, it doesn't mean that those
changes are saved.  If you are doing something that causes XL to lock upand
you need to force XL to quit, any changes that you made to the personal.xls
will be lost.  To manually save the changes, make sure any item in the
"VBAProject (PERSONAL.XLS)" in Project Explorer is selected and click the
Save button on the VBE toolbar (if you hover your mouse over the save
button, a tooltip will pop up saying "Save PERSONAL.XLS (Ctrl+S)").  When
you make changes to the personal.xls and forget to save them, when you go to
close XL, it will remind you that changes have been made and ask you if you
want to save them.

Also, depending on your Security level setting in XL, you may not be able to
run any macros/code that you create.  To check the setting, in XL click
Tools menu > Macro > Security... and then I would choose the Medium setting
on the Security Level tab.

Now, every time you open a file with macros/code in it, you will be asked to
enable/disable macros in a Security Warning.  If you ever receive a file
from someone you don't know/trust, you should disable the macros.  They
potentially could cause damage.

Now that this code is save in personal.xls, it will be available for you to
use whenever you have XL open.  And you don't even have to open the VBE to
use it.  When you are working on a workbook that has hyperlinks that you
want to change:
1.  select the hyperlinks
2.  click the Tools menu > Macros > Macros... (or do [Alt] + [F8] key
combination)
3.  select either "All Open Workbooks" or "PERSONAL.XLS' from the "Macros
in:" dropdown box
4.  select the "PERSONAL.XLS!RemoveLinks" macro from the list box and click
the Run button (or double-click the macro from the list box).

AND BINGO!!!  all of the selected email hyperlinks will be replaced withthe
email addresses.

HTH,

Conan

See my response to your last post....

Seemore,
I have a solution but it is a little more complicated than the previous
one,
but it still uses the VBE.
First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.
Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):
Option Explicit
Sub RemoveLinks()
Dim prngCell As Range
For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")
Next prngCell
End Sub
10. Run this code (hit the [F5] key or click the play button on the tool
bar...it is a green button that looks like a play button on a
CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work correctly,
then you can close the VBE.
As long as you HAVE SELECTED ALL OF THE CELLS that you want to change
the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and remove
the
link.
Please write back if you have any problems.
HTH,
Conan
On Jan 30, 9:00 pm, "Conan Kelly"
Seemore,
Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the
next
line) in the VBE when you do this.
That code should delete all of the hyperlinks in column A of the
active
worksheet of the active workbook.
To make things easier, make sure there is only one XL file

...

read more »- Hide quoted text -

- Show quoted text -

Your directions are excellent. One last thing, if you don't mind. Is
it possible to search for duplicates? With all of these email
addresses now, I want to make sure there are no dupes. I can sort
them but it takes forever to go through a couple of thousand addresses
looking for duplicates. Thanks again for all your help.

 

[Conan Kelly]

Seemore,

Lets say you have the lable "E-Mail Address" in A1 and the actual email
addresses in cells A2:A2000.

After sorting, in cell B3 (if Column B is not being used...if so, pick any
other unused column...just make sure to start in the cell in row 3 of that
column), enter the following formula:

=if(A3=A2,"Duplicate","")

Copy/fill that formula down to the end of the list of email addresses (Cell
B2000 in my example). Then you could put the lable "Duplicates" in cell B1,
turn on autofilter (select any cell (or the whole range) in A1:B2000, Data
menu > Filter > AutoFilter, then filter the dups column for "Duplicate").

You could also do a conditional formatting so that duplicates are
highlighted so they are easier to see. This will also require the list to
be sorted.
--Select cells A3:A2000
--Conditional Formatting (Format menu > Conditional Formatting...)
--Change "Between" dropdown to "equal to"
--In the textbox to the right of "equal to", enter "=A2" (with out the
quotes) or click cell A2 (be sure to remove "$" if XL puts them in).
--Click the "Format" button and select a formatting that stands out (I
usually select bold red font and a yellow fill color for the cell (Patterns
tab).

If you would rather have this automated with code, that can be done, just
let me know.

HTH,

Conan

Seemore,

...or is there a way to save it?

This code becomes part of the workbook/file you created the module in.
When
you close and save the workbook, it is saved.

.... Everytime I create a new worksheet, do I
have to create another vba? for each one ...

That depends on what you mean by "a new worksheet".

Let me go over my lingo for XL (whether or not it is the correct lingo,
that
is another matter). I use the terms workbook and file interchangeably. For
me, an Excel file is a workbook, a workbook is an Excel file. A
workbook/file contains worksheets. Many people refer to worksheets as
"tabs" because each worksheet does have a tab in the lower left corner of
the XL window. The tab has the worksheet's name on it (and you can change
its name by double-clicking the tab) and you can change its color (If I
could, I would try to get everyone to refer to worksheets as "worksheets"
and quit using "tabs"). And those people, who usually call sheets "tabs",
will sometimes call workbooks/files "worksheets".

If you mean creating a new worksheet in the workbook/file we added this
code
to when you say "create a new worksheet", then no. The code is part of the
file and can be used on any sheet in the file.

If you mean creating a new workbook when you say "create a new worksheet",
then not necessarily, but I might help out alot if you were to move that
code somewhere else. Let me explain:

I designed this code to work on the active sheet of the active book. So,
you could have 3 different files open and use this code on any sheet in
any
of the files (you are not limited to using this code in the file where it
is
stored), BUT the file where the code is saved needs to be open. So, if you
want to use this code in another workbook, this workbook where you stored
it
needs to be open.

If that is undesirable, then we can move this code to your "PERSONAL.XLS"
file. The personal.xls file is a file that is usually opened automatically
when XL starts and is hidden so many people don't even know it is there.
It
is meant to put code that is meant to be used on any workbook or to do
other
things not even related to workbooks.

If you want to go this route, then follow my instructions, in one of my
earlier posts, for creating a new module in the VBAProject for your file
using the Project Explorer, but instead of creating this new module in
"VBAProject (Your File Name.xls)", create it in "VBAProject
(PERSONAL.XLS)".
Then you can copy this code into this new module in the personal.xls
vbaproject. If you don't see "VBAProject (PERSONAL.XLS)" in Project
Explorer, then it might not exist yet (I'm not quite sure if it exist by
default or if it is automatically created the first time we record a macro
to it). We can try to create it by recording a macro to it:
1. Flip back to XL
2. Tools menu > Macro > Record New Macro... (hopefully you are not using
XL
2007...if you are, I can't help you too much, but the process will be
somewhat similar)
3. In the "Record Macro" dialog box, the only thing I would change is the
"Store macro in:" dropdown to "Personal Macro Workbook", if it is not
already there. Just leave the default setting for everything else.
4. Click the OK button.
5. A "Stop Recording" toolbar should appear somewhere. Mine only has 2
buttons on it: a Stop Recording button (just a blue square) and a Relative
Reference button and I can only see the first 2 letters and part of the
3rd
of the word "Stop Recording" in the title bar of the toolbar.

Now we are in the Recording Macro mode. Everything you do will be recorded
and XL will generate VBA code to reproduce everything you record.

Now make some changes. It can be just as simple as selecting a different
cell, but you can do anything: type text/numbers/formulas into cells,
change formatting, etc... what ever you want. We aren't going to keep this
macro/code, we are just doing this to create the personal.xls file. When
you are done making changes, hit the stop button on the Stop Recording
toolbar.

Now flip back to the VBE and you should see the "VBAProject
(PERSONAL.XLS)"
in the Project Explorer and it will have a "Modules" folder in it with and
probably a "Module1" in that folder. You could just paste my code into
this
Module1 instead of creating a new module. If the Module1 code window is
not
open, double-click "Module1" in the Modules folder of the "VBAProject
(PERSONAL.XLS)". When you open this module, you will see the VBA code XL
created when you were in recording mode. You can delete that code if you
want...probably won't use it again.

Now that you've added code to the personal.xls, it doesn't mean that those
changes are saved. If you are doing something that causes XL to lock up
and
you need to force XL to quit, any changes that you made to the
personal.xls
will be lost. To manually save the changes, make sure any item in the
"VBAProject (PERSONAL.XLS)" in Project Explorer is selected and click the
Save button on the VBE toolbar (if you hover your mouse over the save
button, a tooltip will pop up saying "Save PERSONAL.XLS (Ctrl+S)"). When
you make changes to the personal.xls and forget to save them, when you go
to
close XL, it will remind you that changes have been made and ask you if
you
want to save them.

Also, depending on your Security level setting in XL, you may not be able
to
run any macros/code that you create. To check the setting, in XL click
Tools menu > Macro > Security... and then I would choose the Medium
setting
on the Security Level tab.

Now, every time you open a file with macros/code in it, you will be asked
to
enable/disable macros in a Security Warning. If you ever receive a file
from someone you don't know/trust, you should disable the macros. They
potentially could cause damage.

Now that this code is save in personal.xls, it will be available for you
to
use whenever you have XL open. And you don't even have to open the VBE to
use it. When you are working on a workbook that has hyperlinks that you
want to change:
1. select the hyperlinks
2. click the Tools menu > Macros > Macros... (or do [Alt] + [F8] key
combination)
3. select either "All Open Workbooks" or "PERSONAL.XLS' from the "Macros
in:" dropdown box
4. select the "PERSONAL.XLS!RemoveLinks" macro from the list box and click
the Run button (or double-click the macro from the list box).

AND BINGO!!! all of the selected email hyperlinks will be replaced with
the
email addresses.

HTH,

Conan

See my response to your last post....

Seemore,
I have a solution but it is a little more complicated than the
previous
one,
but it still uses the VBE.
First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.
Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects).
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click
the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from
"Option Explicit" to "End Sub"):
Option Explicit
Sub RemoveLinks()
Dim prngCell As Range
For Each prngCell In Selection
prngCell = prngCell.Hyperlinks(1).Address
prngCell.Hyperlinks.Delete
prngCell = Replace(prngCell, "mailto:", "")
Next prngCell
End Sub
10. Run this code (hit the [F5] key or click the play button on the
tool
bar...it is a green button that looks like a play button on a
CD/DVD/tape
player/VCR)
(I have tested this code, so it should work for you)
11. Flip back to XL to verify that it worked. If it did work
correctly,
then you can close the VBE.
As long as you HAVE SELECTED ALL OF THE CELLS that you want to change
the
links to email addresses, this code should work. This code will loop
through each cell that is selected, extract the email address and
remove
the
link.
Please write back if you have any problems.
HTH,
Conan
On Jan 30, 9:00 pm, "Conan Kelly"
Seemore,
Any command you enter in the Immediate window of the VBE will affect
what
ever file(s) are open in XL. After you hit the Enter key while the
cursor
is on that line of code, flip back to XL to see if made any changes.
There
won't be any changes (except the flashing cursor moving down to the
next
line) in the VBE when you do this.
That code should delete all of the hyperlinks in column A of the
active
worksheet of the active workbook.
To make things easier, make sure there is only one XL file

...

read more »- Hide quoted text -

- Show quoted text -

Your directions are excellent. One last thing, if you don't mind. Is
it possible to search for duplicates? With all of these email
addresses now, I want to make sure there are no dupes. I can sort
them but it takes forever to go through a couple of thousand addresses
looking for duplicates. Thanks again for all your help.

 

[Seemore]

Seemore,

Lets say you have the lable "E-Mail Address" in A1 and the actual email
addresses in cells A2:A2000.

After sorting, in cell B3 (if Column B is not being used...if so, pick any
other unused column...just make sure to start in the cell in row 3 of that
column), enter the following formula:

    =if(A3=A2,"Duplicate","")

Copy/fill that formula down to the end of the list of email addresses (Cell
B2000 in my example).  Then you could put the lable "Duplicates" in cellB1,
turn on autofilter (select any cell (or the whole range) in A1:B2000, Data
menu > Filter > AutoFilter, then filter the dups column for "Duplicate").

You could also do a conditional formatting so that duplicates are
highlighted so they are easier to see.  This will also require the list to
be sorted.
--Select cells A3:A2000
--Conditional Formatting (Format menu > Conditional Formatting...)
--Change "Between" dropdown to "equal to"
--In the textbox to the right of "equal to", enter "=A2" (with out the
quotes) or click cell A2 (be sure to remove "$" if XL puts them in).
--Click the "Format" button and select a formatting that stands out (I
usually select bold red font and a yellow fill color for the cell (Patterns
tab).

If you would rather have this automated with code, that can be done, just
let me know.

HTH,

Conan

Seemore,

This code becomes part of the workbook/file you created the module in.
When
you close and save the workbook, it is saved.

That depends on what you mean by "a new worksheet".

Let me go over my lingo for XL (whether or not it is the correct lingo,
that
is another matter). I use the terms workbook and file interchangeably. For
me, an Excel file is a workbook, a workbook is an Excel file. A
workbook/file contains worksheets. Many people refer to worksheets as
"tabs" because each worksheet does have a tab in the lower left corner of
the XL window. The tab has the worksheet's name on it (and you can change
its name by double-clicking the tab) and you can change its color (If I
could, I would try to get everyone to refer to worksheets as "worksheets"
and quit using "tabs"). And those people, who usually call sheets "tabs",
will sometimes call workbooks/files "worksheets".

If you mean creating a new worksheet in the workbook/file we added this
code
to when you say "create a new worksheet", then no. The code is part of the
file and can be used on any sheet in the file.

If you mean creating a new workbook when you say "create a new worksheet",
then not necessarily, but I might help out alot if you were to move that
code somewhere else. Let me explain:

I designed this code to work on the active sheet of the active book. So,
you could have 3 different files open and use this code on any sheet in
any
of the files (you are not limited to using this code in the file where it
is
stored), BUT the file where the code is saved needs to be open. So, if you
want to use this code in another workbook, this workbook where you stored
it
needs to be open.

If that is undesirable, then we can move this code to your "PERSONAL.XLS"
file. The personal.xls file is a file that is usually opened automatically
when XL starts and is hidden so many people don't even know it is there.
It
is meant to put code that is meant to be used on any workbook or to do
other
things not even related to workbooks.

If you want to go this route, then follow my instructions, in one of my
earlier posts, for creating a new module in the VBAProject for your file
using the Project Explorer, but instead of creating this new module in
"VBAProject (Your File Name.xls)", create it in "VBAProject
(PERSONAL.XLS)".
Then you can copy this code into this new module in the personal.xls
vbaproject. If you don't see "VBAProject (PERSONAL.XLS)" in Project
Explorer, then it might not exist yet (I'm not quite sure if it exist by
default or if it is automatically created the first time we record a macro
to it). We can try to create it by recording a macro to it:
1. Flip back to XL
2. Tools menu > Macro > Record New Macro... (hopefully you are not using
XL
2007...if you are, I can't help you too much, but the process will be
somewhat similar)
3. In the "Record Macro" dialog box, the only thing I would change is the
"Store macro in:" dropdown to "Personal Macro Workbook", if it is not
already there. Just leave the default setting for everything else.
4. Click the OK button.
5. A "Stop Recording" toolbar should appear somewhere. Mine only has 2
buttons on it: a Stop Recording button (just a blue square) and a Relative
Reference button and I can only see the first 2 letters and part of the
3rd
of the word "Stop Recording" in the title bar of the toolbar.

Now we are in the Recording Macro mode. Everything you do will be recorded
and XL will generate VBA code to reproduce everything you record.

Now make some changes. It can be just as simple as selecting a different
cell, but you can do anything: type text/numbers/formulas into cells,
change formatting, etc... what ever you want. We aren't going to keep this
macro/code, we are just doing this to create the personal.xls file. When
you are done making changes, hit the stop button on the Stop Recording
toolbar.

Now flip back to the VBE and you should see the "VBAProject
(PERSONAL.XLS)"
in the Project Explorer and it will have a "Modules" folder in it with and
probably a "Module1" in that folder. You could just paste my code into
this
Module1 instead of creating a new module. If the Module1 code window is
not
open, double-click "Module1" in the Modules folder of the "VBAProject
(PERSONAL.XLS)". When you open this module, you will see the VBA code XL
created when you were in recording mode. You can delete that code if you
want...probably won't use it again.

Now that you've added code to the personal.xls, it doesn't mean that those
changes are saved. If you are doing something that causes XL to lock up
and
you need to force XL to quit, any changes that you made to the
personal.xls
will be lost. To manually save the changes, make sure any item in the
"VBAProject (PERSONAL.XLS)" in Project Explorer is selected and click the
Save button on the VBE toolbar (if you hover your mouse over the save
button, a tooltip will pop up saying "Save PERSONAL.XLS (Ctrl+S)"). When
you make changes to the personal.xls and forget to save them, when you go
to
close XL, it will remind you that changes have been made and ask you if
you
want to save them.

Also, depending on your Security level setting in XL, you may not be able
to
run any macros/code that you create. To check the setting, in XL click
Tools menu > Macro > Security... and then I would choose the Medium
setting
on the Security Level tab.

Now, every time you open a file with macros/code in it, you will be asked
to
enable/disable macros in a Security Warning. If you ever receive a file
from someone you don't know/trust, you should disable the macros. They
potentially could cause damage.

Now that this code is save in personal.xls, it will be available for you
to
use whenever you have XL open. And you don't even have to open the VBE to
use it. When you are working on a workbook that has hyperlinks that you
want to change:
1. select the hyperlinks
2. click the Tools menu > Macros > Macros... (or do [Alt] + [F8] key
combination)
3. select either "All Open Workbooks" or "PERSONAL.XLS' from the "Macros
in:" dropdown box
4. select the "PERSONAL.XLS!RemoveLinks" macro from the list box and click
the Run button (or double-click the macro from the list box).

AND BINGO!!! all of the selected email hyperlinks will be replaced with
the
email addresses.

HTH,

Conan

See my response to your last post....
On Jan 30, 10:30 pm, "Conan Kelly"
Seemore,
I have a solution but it is a little more complicated than the
previous
one,
but it still uses the VBE.
First of all, select all of the cells that has the hyperlinks that you
want
to convert to email addresses.
Then:
1. Go to the VBE ([Alt] + [F11])
2. If the Project Explorer is not showing, make it visible:
(View > Project Explorer or [Ctrl] + R)
(Project Explorer is usually on the right side of the window)
3. In project explorer, there might be several items listed, but you
should
see something like:
VBAProject (Book8.xls)
(the name of your file will be in the place of "Book8.xls")
4. If you click the plus sign to the left of "VBA Proj..." (expand the
project), you will see at least one folder (Microsoft Excel Objects)..
There
may be a couple others as well (Modules, Forms, etc...).
5. Right-Click the project or any folder/item in the project.
6. Click Insert
7. Click Module
8. A blank window should open up to the right. If not, double-click
the
newly added module.
9. Copy-n-paste the code below into this blank window (everything from

...

read more »- Hide quoted text -

- Show quoted text -

Something is very bizzare. I really couldn't get anything but the
conditional formatting to work properly. In fact, the conditional
programming does not work every time. I found that if I had merged
cells in the worksheet, it didn't work. It may work on one worksheet
and not another. I can't figure it out. Is there a better solution
that works more often?

 

[Conan Kelly]

Seemore,

I found that if I had merged
cells in the worksheet, it didn't work.

I think that you will find on this NG, most people will suggest that you do
not use merged cells. They tend to be difficult to work with when you are
using code or formulas. If it is at all possible, remove the merging.

Is there a better solution that works more often?

Not until we figure out why this solution isn't working correctly. Can you
inspect the cells where you believe they should match and try to determine
why it isn't? Also, make sure to check if your Auto Calculation is turned
on: Tools menu > Options... > Calculation tab > Calculation section > make
sure Automatic option button is selected, then click OK.

Other than that, I can't think of why this wouldn't be working. With out
being able to look at the file myself, I don't think I can be much more help
to you.

Sorry,

Conan

 

[Seemore]

Seemore,


I think that you will find on this NG, most people will suggest that you do
not use merged cells.  They tend to be difficult to work with when you are
using code or formulas.  If it is at all possible, remove the merging.


Not until we figure out why this solution isn't working correctly.  Can you
inspect the cells where you believe they should match and try to determine
why it isn't?  Also, make sure to check if your Auto Calculation is turned
on:  Tools menu > Options... > Calculation tab > Calculation section > make
sure Automatic option button is selected, then click OK.

Other than that, I can't think of why this wouldn't be working.  With out
being able to look at the file myself, I don't think I can be much more help
to you.

Sorry,

Conan




- Show quoted text -

Actually, I think I'm cool now. I had unmerged the file but option 2
was working only sometimes. Option 1 on the other hand, I was doing
it wrong. I didn't quite understand your instructions but after I
reread it, it works perfectly. Option 1 is perfect and that is the
one I'll use. Again, I appreciate all your help.

 

[Conan Kelly]

Seemore,

Glad I could help,

Conan

Seemore,


I think that you will find on this NG, most people will suggest that you
do
not use merged cells. They tend to be difficult to work with when you are
using code or formulas. If it is at all possible, remove the merging.


Not until we figure out why this solution isn't working correctly. Can you
inspect the cells where you believe they should match and try to determine
why it isn't? Also, make sure to check if your Auto Calculation is turned
on: Tools menu > Options... > Calculation tab > Calculation section > make
sure Automatic option button is selected, then click OK.

Other than that, I can't think of why this wouldn't be working. With out
being able to look at the file myself, I don't think I can be much more
help
to you.

Sorry,

Conan




- Show quoted text -

Actually, I think I'm cool now. I had unmerged the file but option 2
was working only sometimes. Option 1 on the other hand, I was doing
it wrong. I didn't quite understand your instructions but after I
reread it, it works perfectly. Option 1 is perfect and that is the
one I'll use. Again, I appreciate all your help.