6

How do I use the SUBSTITUTE formula if the text to be substituted contains a "? For example, if I want to change Hello"Bye to an empty string:

  • Assume Cell A1: Hello"Bye

I have tried both

  • B1=SUBSTITUTE(A1,"Hello"Bye","")

and

  • B1=SUBSTITUTE(A1,""Hello"Bye"","")

Both result in an error because there is a " in the middle of the text confused with the " from the formula.

How can I fix this?

0

2 Answers 2

13

You can escape " by doubling it:

SUBSTITUTE(A1,"Hello""Bye","")

0
0

The previous answer is correct, but can make the line more difficult to read (especially if you have multiple string concatenations).

As an alternative, you can add the specific ASCII value of the character using the char() function. 34 is the ASCII value for a double quote - char(34).

For example, a cell formula ="Hello""Bye" can also be represented as ="Hello" & char(34) & "Bye".

1
  • 8
    IMHO this makes it even hard to read, because you also need to know what char34 is.
    – Nzall
    Jan 26, 2018 at 10:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .