remove quotation marks from a cell

[waldymar]

Hi all,

I have inputs in cells surrounded by quotation marks. I would like to remove quotation marks having pure text only. I tried using SUBSTITUTE formula, however met a problem how to declare that I want to remove quotation marks? Is there any way to remove quotation marks using SUBSTITUTE? If not, is there any alternative excel formulas to do it?

Important: please, don't mention VBA as I want to do it exclusively using excel formulas.

Thanks

 

[pgc01]

Hi

Try:

=SUBSTITUTE(A1,"""","")

 

[dave3009]

Press ctrl+h to bring up Find/Replace

Find: "
Replace: (leave blank)

Click Replace All (or go one by one if there are some you wish to retain)

HTH


Dave

 

[Mike Blackman]

Hi,

I know you have stated you just wanted formula but could a find and replace in Excel not work or do you want to keep the raw data as it is?

 

[Stormseed]

Hi,

Could you not use a simple Find & Replace option in the Edit Menu ?

 

[waldymar]

Thanks to all,

I know I can use Find/Replace tool, but I'm wondering how to do it using formulas.
I also tried before =substitute(A1,"""","") and it doesn't work. Then I even tried to do Find/Replace and it DOESN'T work. So, excel doesn't recognize it as a quotation marks.
A hint, I downloaded my data from web using IMPORT EXTERNAL DATA function from menu DATA. Probably, the problem is that quotation marks from web in excel are not considered quotation marks? Then how to resolve it?

 

[Mike Blackman]

Hi,

What version of Excel are you using? Are they definitely quotes marks " or could they be two ' ? The formula really should work fine.

 

[pgc01]

Hi

The solutions suggested should work for the usual quotes, the character 34.

There are, however, other double quotes. For ex. in documents you use different left and right double quotes characters.

Suggestion:

Edit the cell, select just one of those double quote characters and copy it (CTRL-C)

Then select a range and use Replace, paste (CTRL-V) the character in the Find what:, and leave the Replace with: empty.

 

[waldymar]

Hi Mike,

I'm using Excel 2003. No, it's not double '.
However, I found a solution. I simply copied presumed quotation marks in A1 cell and put in substitute formula. What is surprising it deletes only front presumed quot.marks, then I had to copy the back presumed quot.marks and do double substitute formula. It works))))
=substitute(substitute(A1;"front pres.quot.marks";"");"back pres.quot.marks";"")

Thank you anyway

 

[johnoq2]

The only way I have found to remove the double quote mark "blanks" - especially after copy paste-values of formula results is this: Instead of formula saying, for example, if(a1=0,"problem",""), which will give me some cells saying "problem" and others appearing to be blank (having the "" in them), I use the following formula instead: if(a1=0, "problem", " "). Notice instead of putting 2 quote marks in a row, I am using a quote mark, a space, and another quote mark. Now, when I copy paste special values the results, I can highlight that range, and do a Find and Replace. The Find is for spacebar one time ("space", or "blank"), then Replace with nothing (be sure the cursor is at the first position so that you are replacing with a null). This DOES find all the "blanks" (spaces) and replaces them with nulls successfully. The cells still look "blank" with the formulas in place, or with them copy-paste valued. But this way, I can rid the "" (double quotes) using Find and Replace.