How to remove a " quotation mark from a cell.

I feel like an idiot, but I can't seem to come up with a way to remove a " mark from a cell.  It may be at the beginning, midway or at the end of the cell.  Seems like a pretty simple find and replace or something, but I can't seem to get any formula to find it.

In particular, I was just trying to find the " mark's location in any given cell so I could simply do and left() and right() and then merry them together without the " in the cell.  ie find(Char(34),B2)  Even that is giving me an error though.  Help?  Excel 2007

Thanks-
lindemrmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

regmigrantCommented:
find(char(34),b2) works for me, can you post an example?

Is it possible there's a font/code page problem?
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
backup your workbook

.1. select the cells
.2. press ctrl-H for Find/Replace
.3. Find what: "
.4. leave Replace with empty
.5. click Replace All
0
K BCommented:
use a tilde in the "Find"

~"

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Wayne Taylor (webtubbs)Commented:
Both these formulas are able to find the quotation  mark...

    =FIND("""", A1)
    =FIND(CHAR(34), A1)

However, if you want to remove the quotations, use SUBSTITUTE...

    =SUBSTITUTE(A1, """", "")
0
Sam KCommented:
I think what you are after is the substitute,
In this example I have used cell F7, the formula finds quotations and removes them
=SUBSTITUTE(F7, """","")
0
lindemrmAuthor Commented:
Here's an example segment of the file I'm playing with.  Hopefully one of you can see why the options you're suggesting and that I had already tried aren't currently working.

Thanks!
0
K BCommented:
if you are trying to just rid yourself of quotation marks, do a Find and Replace with tilde and then the characters you are trying to Find.

So for quotation mark ~"
for double quotes ~""
and so on...
0
K BCommented:
i think the file did not attach?
0
lindemrmAuthor Commented:
0
K BCommented:
I see now.. those are not double quotes they are two single quotes

find and replace of this:     ~''       works just fine

Just adjust your formula to remove the need to look for CHAR(34) or use Char(39)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lindemrmAuthor Commented:
well crap. no wonder.   thank you!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.