How to prefix a single quote in a CONCATENATE?

[Steven_Aung]

CONCATENATE("’", [Some column])

You will get an error saying “Number of opened and closed parentheses does not match.”

If you introduce any text around single quote, the error go away. Example, "’ " (space behind the single quote) However, I just need a single quote.

Is it a bug?

Some background. When you add a phone number with a prefix “+” sign like +1234545 via typing in a column, AppSheet will automatically add a single quote prefix in the google sheet cell. However, if you are setting the value via an action, it will not add the single quote. That lead to Google Sheet treating the cell as a formula.

[Steven_Aung]

A workaround for now is introducing a space behind the single quote and trim it.

CONCATENATE(TRIM("’ "), [Some column])

[Ryan_S]

“”""
4 of them

[Steven_Aung]

If four double quotes, you get one double quote but

• ‘’’’ four single quotes, get an empty string.
• “’’” two single quotes surrounded by double quotes, get unterminated string error.
• ‘""’ two double quotes surrounded by single quotes, get one double quote

[Ryan_S · Accepted solution]

Steven_Aung:

‘’’’

My bad. A single quote (or just a quote) where I’m from is "
an apostrophe is ’

Misunderstood the question, sorry…
Try Quote Apostrophe Quote Quote “’”", Just tested here and it yields a single ’
Four Quotation marks yields a single "

[Steven_Aung]

Thanks @Ryan_S. That works.

[MultiTech]

You can get into some weirdness sometimes, with all these quotes and apostrophes - on the backend I mean.

Steven_Aung:

A workaround for now is introducing a space behind the single quote and trim it.

CONCATENATE(TRIM("’ "), [Some column])

This method, or a version of this using LEFT() instead of TRIM(), was suggested by @Aleksi years ago.

[Steven_Aung]

Thanks. It can run faster I guess.

[MultiTech]

Steven_Aung:

It can run faster I guess.

Not necessarily; but I know from experience that stacking quotes and apostrophes can cause strange things sometimes.

[Marc_Dillon]

I just wanted to add the tag words
escape , escaped, escaping
To this thread for future searches, don’t mind me.