Description: I'm trying to SUBSTITUTE '"foo"' (including the double quotes) with "bar", but I can't find out how to escape the double quotes. https://help.libreoffice.org/Calc/Text_Functions#SUBSTITUTE isn't helpful. Steps to Reproduce: =SUBSTITUTE(A1, "\"foo\"", "bar") Actual Results: Err:508 Expected Results: A1 with '"foo"' replaced by "bar". Reproducible: Always User Profile Reset: No Additional Info: Quoting the search text using single quotes doesn't work. User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/62.0.3202.89 Safari/537.36
I was unable to achieve this. According to this: https://wiki.documentfoundation.org/EscapingCharacters we express a single double quote by 4 double quotes in a row. Apparently this is the same in Excel. I also found information that you can use CHAR(34) to express the double quote. I tried: =SUBSTITUTE(A1;"CHAR(34)fooCHAR(34)";"bar") But it just gives me the contents of A1 unchanged. I get the same result with 5 double quotes in a row: =SUBSTITUTE(A1;"""""foo""""";"bar")
4 double quotes is nonsense. In any literal string in formula expressions, a double quote is escaped by doubling it. The 4 quotes in the example are a literal string containing *one* quote, i.e. the enclosing double quotes and in the middle the escaped double quote. For the example given here it should be =SUBSTITUTE(A1, """foo""", "bar")
(In reply to Eike Rathke from comment #2) > For the example given here it should be > =SUBSTITUTE(A1, """foo""", "bar") Dan: does this work for you? I just get the contents of A1 again (I already tried this variant before making my comment 1.
Doubling the double quote works. Thank you. My request is to add how to properly escape double quotes to the documentation for text functions at https://help.libreoffice.org/Calc/Text_Functions, since it's clearly not obvious, as the discussion about four double-quotes in this ticket proves.
(In reply to Buovjaga from comment #3) > (In reply to Eike Rathke from comment #2) > > For the example given here it should be > > =SUBSTITUTE(A1, """foo""", "bar") > > Dan: does this work for you? I just get the contents of A1 again (I already > tried this variant before making my comment 1. My problem was because I had autocorrected typographic quotes in the string: ”foo”
Created attachment 137929 [details] Sample showing no bug I think the issue is with the search text not having double quotes as such but as delimiters. A1=CHAR(34)&"foo"&CHAR(34) = "foo" =SUBSTITUTE(A1;"""foo""";"""bar""";1) = "bar" A1="""foo""" = "foo" =SUBSTITUTE(A1;"""foo""";"""bar""";1) = "bar" It works as @Eike indication.
Resolved as not a bug, please if you are not agree reopen it.
The bug was to document at https://help.libreoffice.org/Calc/Text_Functions how to escape double quotes. Reopening.
Created attachment 173041 [details] Suggested text to be added to Text Functions help page The attached Writer file contains a proposed new section that could be added at the top of the relevant help page (text/scalc/01/04060110.xhp).
https://git.libreoffice.org/help/+/a79ac40adfad119a8957200defabe16282ea5204%5E%21