How to Extract an Apostrophe ' from text

When trying to remove an apostrophe from this text:

[Dimensions] = "100'"

using this formula

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

I get this error: UNTERMINATED STRING

Is there a way for appsheet to not interpret the โ€™ as a single quote? An escape character or something?

Another oddity, If I add a space after the apostrophe, then it clears the error. Very curious behavior???

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

Solved Solved
0 22 2,519
1 ACCEPTED SOLUTION

@Stefan_Quartemont This should do itโ€ฆ SUBSTITUTE([DIMENSION]&, LEFT("โ€™ โ€œ, 1),โ€")

View solution in original post

22 REPLIES 22

MultiTech
Participant V

Does it process correctly?

Nope

Steve
Participant V

Try doubling the apostrophe:

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

Naw that would be interpreted as either the two single-apostrophes or blank.

This is likely something that @Phil should look at.

Youโ€™d think, wouldnโ€™t yaโ€ฆ

And youโ€™d be rightโ€ฆ Drat!

I though I saw somewhere someone claim apostrophes had to be doubled-up inside quotes. Nope.

Probably he was speaking about quotes; these can be doubled to be escaped. 

Why don't we just have a typical escape with "\"? I'd think it would be easy to implement.

Edit: Sorry, bad idea. One cannot just change syntax without studying backward compatibility. 

Bahbus
Participant V

While playing around with it, it only seems to be an issue with trailing apostrophes. As soon as something follows it, there is no issue errors. So, this seems like something that should be fixed in the backend.

I have a possible workaround depending on your data. If any excess apostrophes are always at the end of your data, you could first use concatenate to add a letter or something to the end. Then, you could use the substitute to remove the apostrophe plus whatever you concatenate.

Jervz
Participant III

Hi @Stefan_Quartemont, you can try this while single quote is not working in subtitute yet.

SUBSTITUTE([Dimensions], RIGHT([Dimensions], 1), "") 

or

SUBSTITUTE([Dimensions], LEFT([Dimensions], 1), "")

Cheers

@Jervz and @Bahbus, thanks for the suggestions. Unfortunately in this case, the apostrophe may not always be present. So I have to have something more conditional.

Dave, were you able to replace " 'A " in a string with SUBSTITUTE()? My attempts to remove the โ€™ by adding a trailing character have not actually worked. The expression shows as valid, but it doesnโ€™t actually remove the apostrophe in tests.

I donโ€™t think this is going to work.

You can work with double quotes, but not single quotes; the system uses single quotes to determine start and end points for text strings. BUT it also uses double quotes to interpret text vs. [formula parts] also, so it getโ€™s weird.

Iโ€™ve learned, from trying to create a small sentence that summarizes what happened in a record, that you canโ€™t really work with single quotes.

You can encapsulate a double quote in single quotes and the system knows to write the double quote at part of text - because single quotes force something to the text type.

For instance:

Concatenate(
"The response said the following, ", 
'"', 
[Response_Text], 
'"'
)

Would result in:

The response said the following, "Whatever the result text was."

What happens when you try and switch things around? If instead of using single quotes to encapsulate the double quote, instead use double quotes to encapsulate a single quote? It getโ€™s weird. lol


@Stefan_Quartemont may I ask what youโ€™re trying to do? Perhaps thereโ€™s a work around.

@MultiTech_Visions The idea is that someone can enter a room dimension value (10x10) into a text field and then to following column extracts the numbers out of it and multiplies them to give the resulting Area value.

Iโ€™m stripping all spaces and expected non numerics except for x and then using x as a spilt point to take my numbers and multiply them.

As a work around, I set the initial value of that field as 1 x 1; hoping that users would not enter โ€™ to denote feet, seeing as all measurements are assumed to be in feet anyway.

I could also just have two number fields - but whereโ€™s the fun in that?!?

I would almost never allow users any kind of freedom to put whatever they want in a box. Itโ€™s just asking for dumb and stupid people to break and ruin everything. Outside of something like a longtext box for comments or something.

If you know it needs to be numbers so that you can multiply them together, I would continue to strongly suggest you abandon the idea of letting them put whatever they want and you trying to use code to parse out anything you donโ€™t want. Youโ€™ll save a lot of potential future headaches by just forcing it to be two separate numeric fields with Valid_If enforcement. This is good practice to learn to do weird, complex things. But since you arenโ€™t forced to go that route, I wouldnโ€™t for release. Especially since it is probably impossible to enforce that field to contain two separate numbers at all.

What happens if they write โ€œ10โ€™2"x11.5โ€™x8โ€™โ€ or some combination of ridiculous numbers like โ€œ93184.8763x3845287.1369โ€? Both of these shouldnโ€™t be allowed in the first place, but it would be difficult to parse and enforce on a text field.

@Bahbus - youโ€™re completely right.

This is not a long term solution. This will break and will almost inevitably have to be 2 number fields.

Iโ€™m just enjoying the text parsing exercise in the meantime.

Locating an Apostrophe is still something Iโ€™d like to be able to do in case itโ€™s necessary in the future.

Fair. I did say it was good practice.

What about determining if itโ€™s the last char, comparing find and lenโ€ฆ

The tough part is that you canโ€™t select for the apostrophe in expressionsโ€ฆ Neither in substitute or find. @Aleksiโ€™s suggestion does successfully allow you to isolate the apostrophe in AppSheet expressions, though. Great hack!

Jervz
Participant III

How bout this?

SUBSTITUTE([Dimensions],TRIM(" ' "), "")

or

SUBSTITUTE([Dimensions],TRIM("' "), "")

I used TRIM to remove the space and leaves only the apostrophe. Then the SUBSTITUTE will see apostrophe valid and working.

@Jervz great! This works perfect for us, thank you

Bahbus
Participant V

โ€ฆwaitโ€ฆ
If AppSheet interprets

โ€˜make all of this text no matter what weird symbols it contains &$(@/-)โ€™

As you say it doesโ€ฆ

What if you use three single quotes? Substitute โ€˜โ€™โ€™

@Stefan_Quartemont This should do itโ€ฆ SUBSTITUTE([DIMENSION]&, LEFT("โ€™ โ€œ, 1),โ€")

@Aleksi A wizard, as always.

@Bahbus, this would leave one un-terminated single quote, and the system would show an error.

A wizard indeed! Gonna give that a shot today!

Thanks everyone for pitching in.

Top Labels in this Space