How to delete text prefix ' ?
 

As a result of exporting my Contacts from Outlook 2002 I have a column
in Excel 2000 that looks like this:
https://dl.dropboxusercontent.com/u/...-FormatURL.jpg

I WANT the entries to be dynamic email links like these:




The edit box contains the string with the usual text prefix '. If I
delete that then the cell becomes correctly formatted. So I thought I
could simply select the whole column of several hundred entries and
use the Replace command to replace ' with nothing. But it fails to get
any hits. Why? More important, what's the best way to make the changes
please?

--
Terry, East Grinstead, UK

How to delete text prefix ' ?
 

Hi Terry,

Am Mon, 14 Mar 2016 13:41:42 +0000 schrieb Terry Pinnell:

The edit box contains the string with the usual text prefix '. If I
delete that then the cell becomes correctly formatted. So I thought I
could simply select the whole column of several hundred entries and
use the Replace command to replace ' with nothing. But it fails to get
any hits. Why? More important, what's the best way to make the changes
please?

the prefix is readonly and cannot be deleted with replace method.
You can get rid of it clearing all formats. But the text doesn't change
to a valid email address.
Try it this way (for addresses in column A):

Sub Test()
Dim rngC As Range
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
rngC.ClearFormats
.Hyperlinks.Add _
anchor:=rngC, _
Address:="mailto:" & rngC.Text, _
TextToDisplay:=rngC.Text
Next
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

How to delete text prefix ' ?
 

Claus Busch wrote:

Hi Terry,

Am Mon, 14 Mar 2016 13:41:42 +0000 schrieb Terry Pinnell:

The edit box contains the string with the usual text prefix '. If I
delete that then the cell becomes correctly formatted. So I thought I
could simply select the whole column of several hundred entries and
use the Replace command to replace ' with nothing. But it fails to get
any hits. Why? More important, what's the best way to make the changes
please?

the prefix is readonly and cannot be deleted with replace method.
You can get rid of it clearing all formats. But the text doesn't change
to a valid email address.
Try it this way (for addresses in column A):

Sub Test()
Dim rngC As Range
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
rngC.ClearFormats
.Hyperlinks.Add _
anchor:=rngC, _
Address:="mailto:" & rngC.Text, _
TextToDisplay:=rngC.Text
Next
End With
End Sub


Regards
Claus B.

Thanks Claus, and sorry for my delay in replying - been on holiday.

That works a treat!

--
Terry, East Grinstead, UK