remove quotation marks from a cell

waldymar

Board Regular
Joined
Feb 19, 2009
Messages
238
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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,002
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
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

Banned
Joined
Sep 18, 2006
Messages
3,274

ADVERTISEMENT

Hi,

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

waldymar

Board Regular
Joined
Feb 19, 2009
Messages
238
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

Well-known Member
Joined
Jun 8, 2007
Messages
2,494

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
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

Board Regular
Joined
Feb 19, 2009
Messages
238
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

New Member
Joined
Mar 22, 2012
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,935
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top