Getting rid of double quote marks using =SUBSTITUTE function

[Sophie.Durrant]

Hi,

I have a sheet which is updated monthly and part of it takes out punctuation in the text string in column A. I have done this using this function:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(N2,".",""),",","")," - "," "),"- "," "),"-"," "),"(",""),")",""),"'","")," AND "," & ")," ET "," & ")," UND "," & ")," / "," "),"/","")

I can't get it to substitute double quote marks, because I would need to put """ and it recognises the first two as the beginning and end of the text I want to substitute and then counts the third one as an error. Is there a way to do this using the substitute function or do I need a different way to do it?

Thanks

[XOR LX]

Hi,

Try using four in total, e.g.:

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

Regards

[Norie]

Have you tried using CHAR(34)?

[Sophie.Durrant]

Both of these worked, thank you!