Find & Replace apostrophes in text

[R. Ian Lee]

Hello,

I have a worksheet that has some long text in a column of cells. Some of
the words contain right-slant apostrophes ( ’ ) which cause problems with the
program that imports this data. Since there are many instances of this
character that I need to replace with a standard apostrophe ( ' ), I thought
I would do a find & replace to clean up the data. However, Excel will only
find this character if I tell it to find in the "values". The replace only
allows me to search on "formulas" and therefore doesn't find the characters
to be replaced...

Also, I have discovered that if I do get it to find some text it then
complains about the formula being too long...

Anyone know a solution to these problems?

Thanks!

Ian

 

[John Lyons]

Have you tried dragging the mouse over the right-slant apostrophe, copying
it, then used find & replace and pasting the RH apostrophe in the find box?

John

www.magicspreadsheets.com

 

[R. Ian Lee]

Yes. That's what I've been doing and Excel finds no matches. It will work
if I "Look In" values during a Find. But when doing a Replace, I only get
the option to "Look In" formulas. It can't seem to find the character in
formulas.

Thanks,

Ian

 

[Jim Thomlinson]

I have not tested it but you could try using the tilde character in front of
the apsotrophe. The tilde (~) allows you to look for special characters like
the astrix...

 

[reno]

you might check an excellent add-in from cpearson(dot)com[substitute . for
dot cell view then you can type in even not printing characters and
find/replace works- very helpful if importing data from websites that may
have funky formating

 

[R. Ian Lee]

I'd already tried that too. Doesn't work.

Thanks,

Ian

 

[R. Ian Lee]

I really need to do this w/o an add-in since I'm going to need to pass this
information on to an analyst who will be making the actual changes. I'd
prefer to not have to tell them to go install something... But, I'll
certainly keep this in mind if there's no other solution.

Thanks!

Ian

you might check an excellent add-in from cpearson(dot)com[substitute . for
dot cell view then you can type in even not printing characters and
find/replace works- very helpful if importing data from websites that may
have funky formating

I have not tested it but you could try using the tilde character in front of
the apsotrophe. The tilde (~) allows you to look for special characters like
the astrix...