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?

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

--

Regards,
Nigel
(E-Mail Removed)



"Seemore" <(E-Mail Removed)> wrote in message
news:38ff19d3-42d0-4be1-8035-(E-Mail Removed)...
>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?

On Jan 29, 3:25*am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
> Columns("A:A").Hyperlinks.Delete
>
> --
>
> Regards,
> Nigel
> nigelnos...@9sw.co.uk
>
> "Seemore" <langco...@hotmail.com> wrote in message
>
> news:38ff19d3-42d0-4be1-8035-(E-Mail 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?

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





"Seemore" <(E-Mail Removed)> wrote in message
news:8de67092-2417-45cb-9157-(E-Mail Removed)...
On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
> Columns("A:A").Hyperlinks.Delete
>
> --
>
> Regards,
> Nigel
> nigelnos...@9sw.co.uk
>
> "Seemore" <langco...@hotmail.com> wrote in message
>
> news:38ff19d3-42d0-4be1-8035-(E-Mail 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?

On Jan 29, 8:57*pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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
>
> "Seemore" <langco...@hotmail.com> wrote in message
>
> news:8de67092-2417-45cb-9157-(E-Mail Removed)...
> On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
>
>
>
>
> > Columns("A:A").Hyperlinks.Delete
>
> > --
>
> > Regards,
> > Nigel
> > nigelnos...@9sw.co.uk
>
> > "Seemore" <langco...@hotmail.com> wrote in message
>
> >news:38ff19d3-42d0-4be1-8035-(E-Mail 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 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.

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" <(E-Mail Removed)> wrote in message
news:cd57d497-9df6-4a89-a95d-(E-Mail Removed)...
On Jan 29, 8:57 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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
>
> "Seemore" <langco...@hotmail.com> wrote in message
>
> news:8de67092-2417-45cb-9157-(E-Mail Removed)...
> On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
>
>
>
>
> > Columns("A:A").Hyperlinks.Delete
>
> > --
>
> > Regards,
> > Nigel
> > nigelnos...@9sw.co.uk
>
> > "Seemore" <langco...@hotmail.com> wrote in message
>
> >news:38ff19d3-42d0-4be1-8035-(E-Mail 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.

On Jan 30, 9:00*pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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" <langco...@hotmail.com> wrote in message
>
> news:cd57d497-9df6-4a89-a95d-(E-Mail Removed)...
> On Jan 29, 8:57 pm, "Conan Kelly"
>
>
>
>
>
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > 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
>
> > "Seemore" <langco...@hotmail.com> wrote in message
>
> >news:8de67092-2417-45cb-9157-(E-Mail Removed)...
> > On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
> > > Columns("A:A").Hyperlinks.Delete
>
> > > --
>
> > > Regards,
> > > Nigel
> > > nigelnos...@9sw.co.uk
>
> > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > >news:38ff19d3-42d0-4be1-8035-(E-Mail 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 the hyperlink to text,
however, I was trying extract the actual address from the hyperlink.
example, hyperlink "bob_james" covert to (E-Mail Removed)

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" <(E-Mail Removed)> wrote in message
news:467cac0f-8d19-460e-9ce3-(E-Mail Removed)...
On Jan 30, 9:00 pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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" <langco...@hotmail.com> wrote in message
>
> news:cd57d497-9df6-4a89-a95d-(E-Mail Removed)...
> On Jan 29, 8:57 pm, "Conan Kelly"
>
>
>
>
>
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > 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
>
> > "Seemore" <langco...@hotmail.com> wrote in message
>
> >news:8de67092-2417-45cb-9157-(E-Mail Removed)...
> > On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
> > > Columns("A:A").Hyperlinks.Delete
>
> > > --
>
> > > Regards,
> > > Nigel
> > > nigelnos...@9sw.co.uk
>
> > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > >news:38ff19d3-42d0-4be1-8035-(E-Mail 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 the hyperlink to text,
however, I was trying extract the actual address from the hyperlink.
example, hyperlink "bob_james" covert to (E-Mail Removed)

On Jan 30, 10:30*pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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" <langco...@hotmail.com> wrote in message
>
> news:467cac0f-8d19-460e-9ce3-(E-Mail Removed)...
> On Jan 30, 9:00 pm, "Conan Kelly"
>
>
>
>
>
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > 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" <langco...@hotmail.com> wrote in message
>
> >news:cd57d497-9df6-4a89-a95d-(E-Mail Removed)...
> > On Jan 29, 8:57 pm, "Conan Kelly"
>
> > <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > > 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
>
> > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > >news:8de67092-2417-45cb-9157-(E-Mail Removed)....
> > > On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
> > > > Columns("A:A").Hyperlinks.Delete
>
> > > > --
>
> > > > Regards,
> > > > Nigel
> > > > nigelnos...@9sw.co.uk
>
> > > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > > >news:38ff19d3-42d0-4be1-8035-(E-Mail 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 bob.ja...@abc.org- 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.

On Jan 30, 10:30*pm, "Conan Kelly"
<CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> 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" <langco...@hotmail.com> wrote in message
>
> news:467cac0f-8d19-460e-9ce3-(E-Mail Removed)...
> On Jan 30, 9:00 pm, "Conan Kelly"
>
>
>
>
>
> <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > 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" <langco...@hotmail.com> wrote in message
>
> >news:cd57d497-9df6-4a89-a95d-(E-Mail Removed)...
> > On Jan 29, 8:57 pm, "Conan Kelly"
>
> > <CTBarbarinNOS...@msnNOSPAM.comNOSPAM> wrote:
> > > 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
>
> > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > >news:8de67092-2417-45cb-9157-(E-Mail Removed)....
> > > On Jan 29, 3:25 am, "Nigel" <nigel-...@nosupanetspam.com> wrote:
>
> > > > Columns("A:A").Hyperlinks.Delete
>
> > > > --
>
> > > > Regards,
> > > > Nigel
> > > > nigelnos...@9sw.co.uk
>
> > > > "Seemore" <langco...@hotmail.com> wrote in message
>
> > > >news:38ff19d3-42d0-4be1-8035-(E-Mail 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 bob.ja...@abc.org- Hide quoted text -
>
> - Show quoted text -

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