CODEWITHSUNDEEP
excelworksheet-function
Excellll
Excellll

Reputation: 5785

How can I substitute quotation marks in Excel with SUBSTITUTE formula?

I have worksheet where I need named ranges to correspond to the contents of another cell. The text in the cell is something like:

Partitions w Studs 16" oc

Named ranges cannot have spaces, or most importantly, special characters like ". So, the range is named the following:

PartitionswStuds16oc

To change the former into a reference to the latter in the worksheet, I can handle removing the spaces with the following formula:

=SUBSTITUTE(B1," ","")

I cannot, however, substitute the " because the double-quotation mark is used to specify text in the formula. Excel can't parse the following formula, as expected:

=SUBSTITUTE(SUBSTITUTE(B1," ",""),""","")

Any tips on how to get around this? I know I could change the text to say 16-in. instead of 16", but I want to keep it as my client requested if possible.

Upvotes: 38

Views: 82521

Answers (3)

Howard
Howard

Reputation: 39197

Try using CHAR(34) as substitute for ":

=SUBSTITUTE(B1,CHAR(34),"")

Upvotes: 16

Alex K.
Alex K.

Reputation: 175826

"""" escapes a ":

=SUBSTITUTE(SUBSTITUTE(B1," ",""), """", "")

Upvotes: 58

Codo
Codo

Reputation: 78865

To use double qutoes within a quoted string, just double them. In your case, this results in four consecutive double quotes:

=SUBSTITUTE(B1,"""","")

Upvotes: 3

Related Questions