I'm trying to use the SUBSTITUTE function to replace a double quote (") with
a regular text. The function works well with other special characters but
not the double quote.

Use 4 double quotes: """"

A1 = "Biff"

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

returns: Biff

Biff

"Florence" wrote in message
...
I'm trying to use the SUBSTITUTE function to replace a double quote (")
with
a regular text. The function works well with other special characters but
not the double quote.

TRY:

=SUBSTITUTE(A1,"""","your_text*")

"Florence" wrote:

I'm trying to use the SUBSTITUTE function to replace a double quote (") with
a regular text. The function works well with other special characters but
not the double quote.

Try this:

=SUBSTITUTE(A1,CHAR(34),"xxx")

With the string "Hello" in A1, this returns xxxHelloxxx

Hope this helps.

Pete
On Feb 27, 7:06 pm, Florence
wrote:
I'm trying to use the SUBSTITUTE function to replace a double quote (") with
a regular text. The function works well with other special characters but
not the double quote.

Use CHAR(34) everywhere you want a double-quote:

Example:

=SUBSTITUTE(G14,CHAR(34),"#")

Florence wrote...
I'm trying to use the SUBSTITUTE function to replace a double quote (")
with a regular text. The function works well with other special
characters but not the double quote.

A1: "This" is a "test".
A2: =SUBSTITUTE(A1,"""","|") returns |This| is a |test|.
A3: =SUBSTITUTE(A1,CHAR(34),"|") returns |This| is a |test|.

The SUBSTITUTE function in Excel can be used to replace a specific character or text string with another character or text string. However, when it comes to replacing a double quote ("), there is a bit of a trick to it.

Here's how you can use the SUBSTITUTE function to replace a double quote:

1. Start by typing the SUBSTITUTE function into a cell where you want the replacement text to appear. The basic syntax of the function is as follows:
   
   
   Code:

   =SUBSTITUTE(text, old_text, new_text, [instance_num])

2. In the "text" argument, enter the cell reference or text string that contains the double quote you want to replace.
   
3. In the "old_text" argument, enter two double quotes (""). This tells Excel to look for a double quote character.
   
4. In the "new_text" argument, enter the text string you want to replace the double quote with.
   
5. If you only want to replace a specific instance of the double quote (for example, the second occurrence), you can enter a number in the "instance_num" argument. Otherwise, leave this argument blank.
   
6. Press Enter to complete the function.

Here's an example of how the function would look if you wanted to replace a double quote with the text "replace":

Note that you need to use three double quotes in a row to represent a double quote within a text string in Excel. The first two quotes represent the actual double quote character, while the third quote tells Excel to treat the second quote as a literal character rather than the end of the text string.