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

excel worksheet-function
Excellll picture Excellll · Apr 17, 2011 · Viewed 53k times · Source

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.

Answer

Alex K. picture Alex K. · Apr 17, 2011

"""" escapes a ":

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

Related questions

IF statement: how to leave cell blank if condition is false ("" does not work)

I would like to write an IF statement, where the cell is left blank if the condition is FALSE. Note that, if the following formula is entered in C1 (for which the condition is false) for example: =IF(A1=1,B1,"") …

Read More
Get values from other sheet using VBA

I want to get values from other sheets. I have some values in Excel (sheet2) for example: A B C D - - - - 1 | 2 5 9 12 2 | 5 8 4 5 3 | 3 1 2 6 I sum each column in row 4. I'm working with these values in sheet2 but I …

Read More
Excel formula to get cell color

I would like to know if we can find out the Color of the CELL with the help of any inline formula (without using any macros) I'm using Home User Office package 2010.

Read More