How can the SUBSTITUTE function replace a double quote?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Florence - 27 Feb 2007 19:06 GMT

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.

Reply to this Message

Toppers - 27 Feb 2007 19:17 GMT

TRY:

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

> 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.

Reply to this Message

T. Valko - 27 Feb 2007 19:17 GMT

Use 4 double quotes: """"

A1 = "Biff"

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

returns: Biff

Biff

> 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.

Reply to this Message

Pete_UK - 27 Feb 2007 19:18 GMT

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 <Flore...@discussions.microsoft.com>
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.

Reply to this Message

robotman@gmail.com - 27 Feb 2007 19:18 GMT

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

Example:

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

Reply to this Message

Harlan Grove - 27 Feb 2007 19:21 GMT

Florence <Flore...@discussions.microsoft.com> 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.

Reply to this Message