Expression for Exact Match

NASA1 · 12-29-2019 01:54 PM

Is there an expression that will find an exact match? I need to see if a value in one column is in another column, and then carry out a formula if it is. Contains() returns partial matches. For example, if the value I’m trying to match is “7”, and the column I’m looking in to match contains “777”, the resulting formula is executed. But I only want to match the 7 exactly, not 7 to 777. Thanks!

Steve

The = operator will mostly exact match (upper- and lower-case are equivalent; i.e., it’s case-insensitive), but it does match both content and length. For example:

7 = 7 is TRUE
7 = 777 is FALSE
"a" = "a" is TRUE
"a" = "A" is TRUE
"a" = "aa" is FALSE

Also be aware of a peculiarity of the = operator: if the left-side operand is a blank value, the expression will be TRUE regardless of the right-side operand, but the opposite is not true:

"" = "hello" is TRUE
"hello" = "" is FALSE

Therefore, it’s best to put a value you know will not be blank on the left side unless you specifically want to take advantage of this behavior.

NASA1

Thanks for the quick reply. Could you say a little more about using = with contains? Let’s say that column A has the value I want to find/match in column B. Column A’s value is 7, and in Column B are the values, 99 = Other, 777 = Next. Column A’s value could range from 1-12, 99, and 777. I want a way to check if Column A’s value is in Column B. I can’t use = because Column B contains more than just one value. Hence, I’m using contains. But it does partial matches. So when Column A = 7, it sees the first 7 in 777 and runs a formula.

I’m assuming you’re suggesting using = with contains, such as if(contains([column B], [column A])=[column A], then do X, else do Y. Is that correct?

Steve

I’m suggesting no such thing. Have you read the documentation for CONTAINS()?

To see whether the value of column A occurs in a list of values in column B, use IN():

For instance:

IN([Column A], [Column B])

(Assuming Column B is of type List or EnumList.)

NASA1

Yes, I read the documentation on CONTAINS() and IN(). Currently, Column B is LongText and pre-filled. Can I easily switch it to List? I can’t find any documentation on Column Type List - only EnumList. Or could I Split Column B and use IN()? Something like
IN([Column A], Split([Column B],","))

Steve

Yep!

NASA1

Well, I just can’t get that to work. What I have now is something like this:
if(contains([column B], concatenate([Column A], " = ")),
right(
[Column B],
len([Column B]) -
(find(concatenate([Column A], " = "), [Column B]) + len(text([Column A])) + 2)
),
“”)

It works as it should, except when values in Column A are 7 or 9 which should evaluate to “”/BLANK, but because there is a 99 = and 777 = in Column B, it partial matches on those.

When I try:
IF(IN([Column A], Split([Column B],",")),
right(
[Column B],
len([Column B]) -
(find(concatenate([Column A], " = "), [Column B]) + len(text([Column A])) + 2)
),
“”)

It gets messed up for values 7, 9, 99, and 777. Column B contains: “99 = XXXX, 777 = YYYY,” it should split into 99 = XXXX and 777 = YYYY and BLANK, then see if value from Column A is in that list. For example, if column A = 99, then Column B should = “XXXX, 777 = YYYY,”, but it returns blank for 7, 9, 99, and 777.

Steve

Okay, now I think I understand what you’re trying to do! Unfortunately, AppSheet expressions aren’t sophisticated enough to allow you to do it easily.

Is there a reason you want the number-to-action mapping described in that LongText value? Rather than just hard-coding the Other and Next numeric codes in your expression?

NASA1

Well, it’s a complicated system and can’t explain the logic for why those values are not hard-coding in an email. That being said, when I changed the formula a little bit to:
IF(IN([Column A], Split([Column B]," = ")),
right(
[Column B],
len([Column B]) -
(find(concatenate([Column A], " = "), [Column B]) + len(text([Column A])) + 2)
),
“”)

just changed the split from comma to =, it almost works. It just gets hung up on values of 9 and 7, everything else works.

It doesn’t make sense if IN() is looking for an exact match. Can you verify that IN() is indeed looking for an exact match? Or almost exact as it is not case sensitive

Doesn’t the SPLIT() by = expression create a virtual array with 3 columns: [99] [XXXX, 777] [YYYY]? And in would look through the 3 columns and see if the value in Column A matches - where 9 or 7 would not match anything and 99 and 777 would? But it seems that it still considers 9 or 7 a match. Why? Or is there something I’m not understanding? Thanks for all your help on this!!!

Steve

IN() looks for a case-insensitive exact match. 777 does not match 777 = Next because 777 is missing = Next.

I creates a List, not a “virtual array”. The list would contain the values, 99 , XXXX, 777, YYYY. Note the spaces around the = are not automatically trimmed.

IN("99", SPLIT("99 = XXXX, 777 = YYYY", "=") would return FALSE because none of the list elements produced by the SPLIT() is exactly 99. If you make the split delimiter = (space, equal, space), though, it would work: IN("99", SPLIT("99 = XXXX, 777 = YYYY", " = "), but only for 99; 777 wouldn’t work in either case.

No.

NASA1

Hi Steve - thanks! This is very helpful in understanding more clearly how these expressions work and how parts of my questions didn’t quite make sense. IN() does in fact look for the exact match (my use of that phrase was off), so that approach doesn’t work.

What about the EXTRACTNUMBERS expression? Could that be used to create a list where the leading and trailing spaces are removed? And does that expression recognize -7 as the number -7?

Steve

EXTRACTNUMBERS() does not recognize signs, so -7 would be seen as 7. The function does recognize real numbers (e.g., 0.1, 12.2). It also recognizes scientific notation (e.g., 7.2e4). It does not recognize numbers immediately adjacent to letters (e.g., i77).

The created list would be: 99, =, XXXX,, 777, =, YYYY. Note the comma terminating XXXX,. IN() would, however, find exact matches for both 99 and 777 .

NASA1

Thank you! Do you have thoughts on which expression (Split or ExtractNumbers) is more efficient?

Steve

I’d expect each to be about the same.

NASA1

Or what about splitting on the space? IN(“99”, SPLIT(“99 = XXXX, 777 = YYYY”, " ") - would this create the list 99,=,XXXX,777,=,YYYY. Then IN() could find an exact match?

NASA1

Is it possible to Split() on more than one delimiter?

Bahbus

This would be significantly easier by creating a dedicated lookup table for any number = code combinations.

NASA1

How does EXTRACTNUMBERS() work exactly. The documentation doesn’t explain in great detail. For example, if I have a string of text “99 = abcd01.1, 777 = ghij12.2” does extract numbers pull out just the whole numbers only (i.e., 99 and 777) or also the numbers within the text parts (i.e., 01.1 and 12.2)? Also, what if there is a negative number, such as -7, will it recognize the number as -7 or just pull out the 7 or not consider it a number?

I’m wondering if this could be a way to deal with this issue. However, when I try it, it still doesn’t seem to work. The only reason is that IN() will actually do partial matches, where if the value to search for is 7 and it sees 777, then it will be true.

This is what I tried:
if(in([Column A], extract(“NUMBERS”,[Column B])), right( [Column B], len([Column B]) - (find(concatenate([Column A], " = "), [Column B]) + len(text([Column A])) + 2) ), “”)

And to make extra clear, I’m just having an issue (I think) with the first part of the formula: if(in([Column A], extract(“NUMBERS”,[Column B])) where any AppSheet expression seems to say that if “7” is in a list with “777” then it’s True.