Extraction challenge

AppSheet AppSheet Q&A
,
1

Hello!
I need to verify a string of text being entered by the user.

It’s valid in any of three formats

  1. any number of numeric digits (eg 36123436722)

  2. any number of numeric digits preceded by one letter (eg X23462123444)

  3. any number of numeric digits preceded by two letters (eg PK34451234222)

I was using valid if: LEN(TEXT(EXTRACTNUMBERS([MLS])))=<2 until I realized this is extracting the digits and then counting them, rather than extracting the digits, throwing them away, and counting the rest

is there a way for me to extract letters?

thanks!

2

If the above vaild_if was counting the extracted numbers, can’t you just subtract this number from the original string length then?

  • LEN([MLS])-LEN(TEXT(EXTRACTNUMBERS([MLS])))<=2
1 Like
3

I believe that would allow the two non-numbers to appear anywhere in the string.

i’ll clarify the criteria:

it can be any length between 8 - 20 characters

case 1 - they’re all numbers,

OR case 2 - the first is a letter and the rest are numbers,

OR case 3 - the first two are letters and the rest are numbers

4

I think something like this will get you close:

  • Ensures all characters beyond the second character are numbers
  • Ensures entry is at least 8 characters long
  • Ensures entry is no more than 20 characters long
  • Ensures the first character is a number or a letter
  • Ensures the second character is a number or a letter

AND(LEN(MID(TEXT([MLS]),3,LEN(TEXT([MLS]))))=LEN(EXTRACTNUMBERS(MID(TEXT([MLS]),3,LEN(TEXT([MLS]))))),LEN(TEXT([MLS])) >= 8,LEN(TEXT([MLS])) <= 20,
IN(UPPER(LEFT(TEXT([MLS]), 1)), {“A”, “B”, “C”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}),IN(UPPER(MID(TEXT([MLS]), 2, 1)), {“A”, “B”, “C”, “D”, “E”, “F”, “G”, “H”, “I”, “J”, “K”, “L”, “M”, “N”, “O”, “P”, “Q”, “R”, “S”, “T”, “U”, “V”, “W”, “X”, “Y”, “Z”, “0”, “1”, “2”, “3”, “4”, “5”, “6”, “7”, “8”, “9”}))

The expression might need some adjustments, but since I don’t have your data and can’t troubleshoot. Let me know if there is something that is off.

2 Likes
5

You can use a combination of REGEXMATCH to validate the formats. Here’s a quick way to do it:

Use this in the Valid If condition:

plaintext
Copy code
OR(
REGEXMATCH([MLS], “^\d+$”),
REGEXMATCH([MLS], “^[A-Z]\d+$”),
REGEXMATCH([MLS], “^[A-Z]{2}\d+$”)
)
This checks if the input is:

All digits.
One letter followed by digits.
Two letters followed by digits.
Hope this helps!

1 Like
6

wait…

we can use RegEx in appsheet ? ?

7

the second character can only be a letter if the first character is. if it starts with a number, they are all numbers

8

BTW Thank you! For the colour coding!!

9

Here’s a taste of the data

40498470
51077240
4018244
245609
592330139
W7279182
V5922531
QW400060038D
40412999
40061990
G5917167
40379547
MK125400001533502
48721839
40362509
103352

10

I think you may want to add more requirements to the criteria.

The above text string has an alphabet at the end. So the characters after first 2 alphabets are not numeric in this particular case.

2 Likes
11

well that sucks! lol
however im thankful that i randomly copied that one with a letter at the end
(i also noticed that the strings can be as low as 6 characters)

i need a new plan of attack.

my goal: the [MLS] needs to be a unique value, we don’t want to enter data we already have, so my Valid_If begins with searching the column to make sure the value has not already been used.

But people here have a long standing fondness for freeform long text, frequently leaving themselves little notes within the data.
For example, they’ll enter this: " MLS# = MK125400001 unverified use PIN 4060184 instead "

So i need to constrain them to just entering the MLS. No notes to self, No alternate values, otherwise it renders the unique validator useless

maybe i could just say: no spaces, no non-alphanumeric, and no more than two letters in a row.

hmmm

12

Thank you. Is it correct that you would continue to want those two letters at the beginning of the text string as you mentioned earlier.