Replacing the Caret character in Excel 2010

Ok maybe it's just early but how do I do this?

I have a spreadsheet from a supplier that has several hundred instances like

Joe Smith ^^

I want to get rid of ^^

 I've tried

^ (function not valid)
^^ (function not valid)
~^ (function not valid) (I thought this should work)
~^~^ (function not valid) (I thought this should work)
/^/^ (Not found)
\^\^ (Not found)

NOW if I do a Find Next or Find All with ^^, I get results.

If I am on the first result and hit Replace the characters are replaced.

If I hit Replace All, I get the function not valid error.

So yes I could do find next, replace, find next replace.... but this sheet has way too many instances of this to do that.

I know this is because of the special superpowers of ^ in excel but surely there must be a way to escape the character??
LVL 36
SidFishesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
NBVCConnect With a Mentor Commented:
I did a quick test and I can do a replace all....

Is it possible to post a sample piece of that workbook to test it on?
0
 
SidFishesAuthor Commented:
hmmm. that's very odd. I copied a block of affected rows into a new spreadsheet so I could post and was able to do the global replace.

Still doesn't work in the original document.

maybe a corrupt worksheet?
0
 
NBVCCommented:
Maybe try doing a copy of the whole worksheet, then paste special Values or Values and Number Formats.  Then try the replace...
0
All Courses

From novice to tech pro — start learning today.