Find & Replace apostrophes in text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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...
 
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 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

reno said:
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

Jim Thomlinson said:
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...
Click to expand...
Click to expand...
 
Back
Top