How can the SUBSTITUTE function replace a double quote?

G

Guest

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

Pete_UK

Try this:

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

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

Hope this helps.

Pete
 
R

robotman

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

Example:

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

Harlan Grove

Florence said:
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.
Click to expand...

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Substitute function yields #VALUE! when add the instance_number 7
How to perform Search and Replace on "Tilde", CHAR(126), ~ 2
How can you convert a text value to a numerical formula ? 16
substitute function 4
Difficulty cleaning some char(63) characters 1
using a single quote ' in SEARCH function 2
Excel Create a New line in a Word Link without double "" around result 0
Trim and Substitute not working 4

Top