How to quote a quote with the CONCATENATE function in OOCalc

Question

In OOCalc I want to use the CONCATENATE function to add quotes to each string in column A.

So in cell B1 I want to do:

=CONCATENATE("\"",A1,"\"")

OOCalc does not like this, or without the escaping backslash.

Does anyone know how to do this, or what an alternative method might be?

score 185 · Accepted Answer · answered May 20 '10 at 08:13

185

This works for me:

=CONCATENATE("""",A1,"""")

Repeating the quotes escapes them (as it does in Visual Basic, I believe), so """" reads as: 'one quote to start a string, one escaped quote (""), then one quote to finish the string'.

answered May 20 '10 at 08:13

Andy Balaam

6,423
6
34
37

14

Works for Google Sheets too – Daniel Golden Aug 07 '15 at 22:06
When you have a complex construction like in a QUERY this methode won't work. In such cases, the @kiran answer does work CONCATENATE(char(34); B2 ;char(34)) – Richard de Ree Sep 27 '18 at 07:58
1

You can simply use ampersand `&` to concatenate too, e.g: `=""""&A1&""""` – stwr667 Oct 27 '21 at 03:02

score 41 · Answer 2 · edited Apr 22 '19 at 16:45

41

Use char(34) to get the quote character.

CONCATENATE(char(34); B2; char(34))

edited Apr 22 '19 at 16:45

Genhis

1,484
3
27
29

answered Nov 18 '13 at 21:42

Kiran

420
4
6

This one works in complex construction needed in queries – Richard de Ree Sep 27 '18 at 07:59

score 16 · Answer 3 · answered Aug 29 '14 at 17:04

16

Identical to the above but without the function:

="""" & A1 & """"

answered Aug 29 '14 at 17:04

Jake Toronto

3,524
2
23
27

score 3 · Answer 4 · answered May 30 '17 at 20:17

3

You can do it in 2 ways,

By using =CHAR(34) in the places of doible quotes eg: =CONCATENATE("coffee",CHAR(34),"code")
By concatenating cell values

Steps

Set the cell value as double quotes -> "
Concatenate that cell in the string, wherever you need double quotes. eg: E1 = " F1 = =concatenate("coffee",E1,"code")

Thank you

answered May 30 '17 at 20:17

Alexis

816
2
11
28

This question has already been answered, and using a simpler method than either of your suggested options. – Graham May 31 '17 at 02:14
1

I implemented the methods mentioned above, but when we try to copy the data from the formatted cell, extra double quotes is appearing in the data. Hence I worked on it and found a better solution, that is what I added here. If it is not useful for you ignore it, there will be some people looking for a better solution. Thank you. – Alexis May 31 '17 at 08:53
That's not how StackOverflow works, as it is a repository of knowledge you don't just ignore things. You also provided none of the details in your comment in your answer. – Graham May 31 '17 at 13:35
I don't know why you are targeting me, after all, I explained it clearly in the second comment. Thank you. – Alexis Jun 06 '17 at 08:55
StackOverflow flagged your answer as low quality, I'm not targeting you. – Graham Jun 06 '17 at 12:51

score 0 · Answer 5 · answered Jul 08 '15 at 17:11

0

You can use single quotations marks within the double quotation marks and vice versa.

answered Jul 08 '15 at 17:11

Steven

790
7
16

How to quote a quote with the CONCATENATE function in OOCalc

5 Answers5