Comparing text with the apostrophe ( ' )

Since this expression is invalid on AppSheet.

[column] = " '"

(Note that any spaces are not allowed in front of the ’ )

I need to prevent user from saving something like,

Height=5 '

( 5’ is preferred)

So I need to include it in Valid If expression.

How to correct it ?

1 Like

Could you please elaborate, what is the valid entry and what is an invalid entry that you wish to prevent the user from data entry and column type etc.?

3 Likes

Samples for valid entry
1’
10’
100’

Samples for invalid entry
1 ’
10 ’
100 ’

Thanks.

1 Like

Thanks. Column type- text?

3 Likes

Yes Sir.

Could you please try the below

AND(

IFS(
LEN( LEFT([_THIS], LEN([_this])-1))=1, NUMBER(LEFT([_THIS], LEN([_this])-1))>0,
LEN(LEFT([_THIS], LEN([_this])-1))=2, NUMBER(LEFT([_THIS], LEN([_this])-1))>=10,
LEN( LEFT([_THIS], LEN([_this])-1))=3, NUMBER(LEFT([_THIS], LEN([_this])-1))>=100),

RIGHT([_this],1)="’ "
)

Is supposed to work for heights till 999’

2 Likes

Actually, I need the following text to be invalid.

The object 's Length=3 ', Width=4 ’ and Height=5 '

The valid text should be,
The object’s Length=3’, Width=4’ and Height=5’

Please advise because your suggested expression is not working with it.

I’d tried to use CONTAINS([_THIS], " '") to catch the invalid input but it errors because of the " '" .

1 Like

Thank you for revised requirements :slight_smile:

In general repeating the shared expression suitable for 1 parameter could be extended for 3.

Could you update what is your setup for this entire column? Is it concatenated text that the user will enter? How the user enters the three parameters?

2 Likes

It’s for note taking columns. So users are freely typing their notes. However I need to make it not looking weird.

There is no any parameters input like you asked.

2 Likes

I am sorry in that case. I will be unable to offer any suggestion for such a free flowing text validation. I believe unless some pattern is there, one may not be able to validate a free following text exactly.

One option could be the users enter the three parameters in three fields and the app constructs a valid string sentence.

My request will be if possible to mention requirements in greater detail so it could ensure evaluation / better solution.

Maybe someone else will have a solution to your requirement.

However, I believe the approach suggested for your earlier requirement of just one field will work for anyone looking for such a requirement.

2 Likes

Thanks for your kind and efforts.

Consider the following expression is working fine in Excel & Google Sheet but not in AppSheet validation.

FIND(" '", “abc 'd”)

If AppSheet just recognized the " '" , the valid expression would be so short like

CONTAINS([_THIS], " '")

2 Likes

I fully agree. It is just matter of CONTAINS() working as desired. I am sure there will be some approach to get it working and someone will definitely have an easier solution the way you mentioned. I will post if I come across any such possibility.

2 Likes

FIND(" “&LEFT(”’ ",1),[_THIS])=0 should do the job.

3 Likes

I use LEFT(" “&”’ ", 2) as work around.

My valid expression is

NOT(OR(
   CONTAINS([_THIS], "' "),
   CONTAINS([_THIS], LEFT(" "&"' ", 2))
))

This prevents users from saving texts like
it ’ s
it’ s
it 's
instead of it’s.

2 Likes

Could you please try in valid_if the following

NOT(IN(" ’ ",SPLIT([_this]) ))

I believe it will work for the two valid , invalid cases you have shared.

Edit: Modified the expression.

The validation is to avoid 3 cases, for example

It  '  s

It'  s

It  's

I don’t think the " ’ " alone would be working as you said. Anyhow, I’ve got my app working now.

Thanks to all of you guys.

2 Likes

Yes, saw your solution post after posting. Great to know you got it solved.

2 Likes

@Aleksi : simple, effective as usual. :slight_smile:

1 Like

Before I saw this, someone on heaven suggested me to use

LEFT(" “&”’ ", 2)

:shushing_face:

1 Like