How to remove apostrophe in the word NURUL'AIN

[Firdaus_AG]

Hi everyone,

Please help me remove the apostrophe from the word NURUL'AIN. The final output should be NURULAIN. I used the following formula, but an error appeared.

SUBSTITUTE([word], "'", "")

Thank you for your help.

[Firdaus_AG]

Error appeared as the following

Expression was unable to be parsed: Number of opened and closed parentheses does not match.

[jballester]

Hi

It is not the same scenario but it may help

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/How-to-Extract-an-Apostrophe-from-text/m-p/2681...

[TeeSee1]

Try

SUBSTITUTE([word],SUBSTITUTE("'L","L",""),"")  //here the L in "'L" and "L" can be any letter.

[Firdaus_AG]

In my case not only 'L there are other cases like 'A and etc

[TeeSee1]

Please look at the expression carefully. It has nothing to do with the words you are converting.

This expression works in general cases.

[Suvrutt_Gurjar]

A nice solution @TeeSee1 

[Steve]

To explain @TeeSee1's solution...

The apostrophe (or single-quote) is a special character in expressions. The reason it's special is because of some deep internal details that AppSheet developers are stuck with. It's not desirable, but it is what it is and cannot easily be fixed. Because it's special, we have to find ways to get around its special-ness.

As you noticed, SUBSTITUTE([word], "'", "") produces an error because the occurrence of the apostrophe in your expression triggers the special-ness. We have to find a way to express "'" (a Text value consisting of a single apostrophe) without including it literally. The solution is to generate that Text value from another Text value. SUBSTITUTE("'L", "L", "") does this by starting with a Text value that includes an apostrophe and some other text ('L), then removes the some other text (L), leaving only the apostrophe. That result is then used in your original expression in place of the literal "'". The L used is arbitrary: you could use any text you want. SUBSTITUTE("'DELETE ME", "DELETE ME", "") would work just as well.

[Koichi_Tsuji · Accepted solution]

The "old" solution Aleksi @AleksiAlkio came up with and shared with the community is 

Substitute([Col with apo], Trim("' "),"")

I always use this technique.

[TeeSee1]

Oh simpler expression. I like this better!