Update TRIM and FIND

[Bahbus]

Hello!

Today, I would like to request updates to the functionality of these two expressions.

AppSheet’s TRIM currently only removes extra spaces at the beginning and end of a string. Excel’s TRIM does the same but also removes all but a single space inbetween words. I would like for AppSheet to match this functionality, perhaps, by adding a optional argument on which version of TRIM is used (I feel like that would help maintain any backward compatibility).

FIND(needle, haystack) is a great little expression. Currently it returns with the character position of the needle within the haystack or 0 if not found. And while it technically seems to work with non-Text columns, it does seem to auto-concat the haystack and treat it as a string anyway. I would like FIND to get updated with handling of lists, in which it returns the INDEX number of the result. We can use IN to get a true/false. We have INDEX to pull a specific piece out of a list. But we have no good way to compute where in a list a particular option is. This will also allow for fun with
INDEX([SomeList], FIND("ME", [SomeList])+1) - Who, if anyone, is after me in this list?

CONTAINS could probably also benefit from this update. IN works to check if there is an exact match within a list. The same kind of update to FIND would also benefit CONTAINS as being a looser way to match in a list. i.e. Does any item in this list contain <needle>?

Basically, on the backend, all I’m asking for is checking if the haystack is a list, and loop through it since us end users can’t create any kind of loops.

[Kirk_Masden]

Thanks for writing this up!

[Aleksi]

Yes I agree. It would be much easier than writing something like…
COUNT(SPLIT(LEFT(LIST(One,Two,Three,Four,Five),FIND([Column],LIST(One,Two,Three,Four,Five))+LEN([Column])-1)," , "))

[Kirk_Masden]

Thanks for this example, @Aleksi! I’d like to study it learn how it works. Thanks for all of your help in 2019. Have a great 2020!

[Bahbus]

I… Don’t even know where to start deconstructing that…
And…yet…I intrinsically understand exactly how it works…

[TDhers]

@hugheshilton some good suggestions for us to consider in 2020…

[Aleksi]

Think about the formula with a real value…
COUNT(SPLIT(LEFT(LIST(One,Two,Three,Four,Five),
FIND(Three,LIST(One,Two,Three,Four,Five))+LEN(Three)-1)," , "))

FIND
It will find the position for the value “Three” from the list… the list is actually a string “One , Two , Three ,…” where the word “Three” starts from the position 13.

LEN
LEN(Three)-1 is counting letters from that word and when we add -1, we will have a number for the LEFT. That result is 13+5-1=17. That’s the number of the last letter in the word “Three”.

LEFT
LEFT(LIST(One,Two,Three,Four,Five),17) is “One , Two , Three”

SPLIT
It will generate a list of values from the string “One , Two , Three”

COUNT
It counts items in that list.

SUMMARY
The whole idea is to find where the value is and filter values after that list away. Then you can know where that value is in that list… in this case the value is 3rd.

[Bahbus]

Truly a God among men with that ironclad logic.

[Bellave_Jayaram]

A simply uncanny genius is all I can say😊

[Former Community Member]

As of today, TRIM() does what Excel does.

[Kirk_Masden]

Great! Thanks!!!