Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I set an 'M' prefix to existing text in a column? muppetlover27 New Users to Excel 2 June 10th 08 01:41 PM
prefix in text box pswanie Excel Programming 4 January 19th 08 04:05 PM
delete a prefix The Rook[_2_] Excel Discussion (Misc queries) 3 February 28th 07 04:28 PM
How to create a prefix containing text and zero? GeneWan Excel Worksheet Functions 10 November 24th 05 04:03 AM
How can I add a two letter prefix to text in cells? Newbs18 Excel Discussion (Misc queries) 2 November 9th 05 05:06 AM


All times are GMT +1. The time now is 10:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"