How To Search And Replace A Special Character In A String
 

I have some part numbers in string format that include the caret (^)
symbol.

For instance: ^^^235-741-22 STAINLESS STEEL NUT

I would like to get rid of the "^" symbol in the string, so that the
part number would be:

235-741-22 STAILESS STEEL NUT

but Excel's Search and Replace does not let me search and replace using
"^^^" (without quotes). It thinks that it is some kind of formula.

I tried putting a tilde (~), an ampersand (&), or just a single quote
(') in front of the search string (ex.: ~^^^) but Excel still does not
like it.

What is the correct way of doing this search and replace action?
--
tb

How To Search And Replace A Special Character In A String
 

Hi,

Am Wed, 4 Nov 2015 15:57:10 +0000 (UTC) schrieb tb:

I tried putting a tilde (~), an ampersand (&), or just a single quote
(') in front of the search string (ex.: ~^^^) but Excel still does not
like it.

search for "^" (without quotes) and replace with nothing.


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

How To Search And Replace A Special Character In A String
 

On 11/4/2015 at 10:49:58 AM Claus Busch wrote:

Hi,

Am Wed, 4 Nov 2015 15:57:10 +0000 (UTC) schrieb tb:

I tried putting a tilde (~), an ampersand (&), or just a single
quote (') in front of the search string (ex.: ~^^^) but Excel
still does not like it.

search for "^" (without quotes) and replace with nothing.


Regards
Claus B.

Well, I'm still getting the error message as Excel seems to interpret
it as a formula...

In the Find And Replace window I enter: "^" (no quotes) in the Find
What box and I leave blank the Replace With box. Then I hit the
Replace All button.

The error window that I get starts with: "We found a problem with this
formula. etc. etc. etc."

--
tb

How To Search And Replace A Special Character In A String
 

Hi,

Am Wed, 4 Nov 2015 19:40:04 +0000 (UTC) schrieb tb:

In the Find And Replace window I enter: "^" (no quotes) in the Find
What box and I leave blank the Replace With box. Then I hit the
Replace All button.

for me it works fine if I select the find box and enter ^ and space.
Or put in the find window Alt+0094
Or try:

Sub Replace()
Range("A1:A100").Replace what:="^", replacement:="", lookat:=xlPart
End Sub


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