Removing a character

[Guest]

I have a column of data that contains addresses that must have been exported
from a database, perhaps Outlook. Some of the addresses have a character at
the beginning of the string that I need to remove. It looks like an
apostrophe (') but when I do a Find I come up empty. I was hoping to do a
Find/Replace but no luck. How can I remove this nasty character?!

tks,
steve

 

[T. Valko]

Try copying the character and then paste it into the Find What box.

Select a cell with the offending character.
In the formula bar, highlight the character
Goto Edit>Copy
Select the range of cells where you want to do the Replace
Goto Edit>Replace
In the Find What box paste the character by hitting CTRL V
Replace with: nothing, leave this blank
Replace All

 

[Bernard Liengme]

Use a helper column with =SUBSTITUTE(A1,CODE(39),"")
The new column will be apostrophe-less
Use Copy | Paste Special and specify Value to convert all the formulas to
text
Now you can delete the original column
best wishes

 

[Guest]

i read it as if u only need it to be removed only at the beginning...i hope
all adresses in your base do not include it somewhere inside a non-nasty
address...

 

[Guest]

Great one Bernard, thanks!!

steve

Use a helper column with =SUBSTITUTE(A1,CODE(39),"")
The new column will be apostrophe-less
Use Copy | Paste Special and specify Value to convert all the formulas to
text
Now you can delete the original column
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email