Re: convert hyperlink to email address

See my response to your last post....




"Seemore" <langcorey@xxxxxxxxxxx> wrote in message
news:1175fa33-a7ca-493d-86f4-a33e77485a70@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 30, 10:30 pm, "Conan Kelly"
<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxxxx> wrote in message

news:467cac0f-8d19-460e-9ce3-b4d74611a36a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 30, 9:00 pm, "Conan Kelly"





<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> 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
work*** 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 *** 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 *** 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...@xxxxxxxxxxx> wrote in message

news:cd57d497-9df6-4a89-a95d-ed70d0283914@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 29, 8:57 pm, "Conan Kelly"

<CTBarbarinNOS...@xxxxxxxxxxxxxxxxxxx> wrote:
Seemore,

1. Make sure the file you want to change is active (you are working in
it).
2. Make sure the *** 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...@xxxxxxxxxxx> wrote in message

news:8de67092-2417-45cb-9157-e3a27a71c032@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jan 29, 3:25 am, "Nigel" <nigel-...@xxxxxxxxxxxxxxxxx> wrote:

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

--

Regards,
Nigel
nigelnos...@xxxxxxxxx

"Seemore" <langco...@xxxxxxxxxxx> wrote in message

news:38ff19d3-42d0-4be1-8035-745e218348d6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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...@xxxxxxxx Hide quoted
text -

- Show quoted text -

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


.