Bug 113976 - Document how to escape double quotes in text functions
Summary: Document how to escape double quotes in text functions
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
Reported: 2017-11-21 16:51 UTC by Dan Dascalescu
Modified: 2018-09-29 19:51 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:

Sample showing no bug (10.01 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-11-22 22:36 UTC, m.a.riosv

Note You need to log in before you can comment on or make changes to this bug.
Description Dan Dascalescu 2017-11-21 16:51:41 UTC
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:  

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
Comment 1 Buovjaga 2017-11-22 17:37:35 UTC
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:

But it just gives me the contents of A1 unchanged.

I get the same result with 5 double quotes in a row:
Comment 2 Eike Rathke 2017-11-22 18:01:05 UTC
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")
Comment 3 Buovjaga 2017-11-22 18:10:35 UTC Comment hidden (obsolete)
Comment 4 Dan Dascalescu 2017-11-22 18:18:53 UTC
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.
Comment 5 Buovjaga 2017-11-22 18:21:38 UTC
(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”
Comment 6 m.a.riosv 2017-11-22 22:36:37 UTC Comment hidden (obsolete)
Comment 7 m.a.riosv 2017-11-22 22:37:26 UTC Comment hidden (obsolete)
Comment 8 Dan Dascalescu 2017-11-22 23:16:36 UTC Comment hidden (obsolete)