Solved: Re: How to Extract an Apostrophe ' from text

Stefan_QREW · 09-05-2019 11:56 AM

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], "' ", "")

Aleksi

@Stefan_Quartemont This should do it… SUBSTITUTE([DIMENSION]&, LEFT("’ “, 1),”")

View solution in original post

MultiTech

Does it process correctly?

Stefan_QREW

Nope

Steve

Try doubling the apostrophe:

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

MultiTech

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

This is likely something that @Phil should look at.

Steve

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.

Joseph_Seddik

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

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

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

Stefan_QREW

@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.

MultiTech

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.

Stefan_QREW

@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?!?

Bahbus

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.

Stefan_QREW

@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.

Bahbus

Fair. I did say it was good practice.

Grant_Stead

What about determining if it’s the last char, comparing find and len…

Stefan_QREW

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

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.

Joachim_Mund

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

Bahbus

…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 ‘’’

Aleksi

@Stefan_Quartemont This should do it… SUBSTITUTE([DIMENSION]&, LEFT("’ “, 1),”")

MultiTech

@Aleksi A wizard, as always.

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

Stefan_QREW

A wizard indeed! Gonna give that a shot today!

Thanks everyone for pitching in.

rasmus

I have this problem also. Many of the serial numbers on products I need to scan with barcode comes in with an apostrophe ' before the serial number, but it does not get saved in the sheet. So when I wanna search for the item in appsheet it does not show up, because it is saved without, but when I use the scan function in the search field it is there.

Any ideas how to solve this?

rasmus

I just used AppSheet Database instead of Sheets. That solved my problem.