MATCH a partial string

[Myles_Kendall1]

I started with help from this support topic:

Formula help for an IF(logical, value1, value2) Formulas

So I’m using a formula in a column to pull data for another table. This is my formula: IF({Test Type} = “CAT 1”, {Date Only}) Which is basically: If the test type is CAT 1, then display the date from the ‘date only’ column. My problem is this… sometimes tests are both CAT 1 AND CAT 5, but I still need it to pull the data. So what I really need is IF test type CONTAINS CAT 1, then display the date from the ‘date only’ column. I used Fieldbook before Airtable and in that instance I was told t…

But I need some help matching a partial string.

In my example:
Students have a set of “awarded units”, which is a lookup field with multiple values.
Those values are matched on a second table to return the “current equivalent” unit, this result is returned in a string.

I then want a set of fields, each dedicated to single unit, to test whether or not that unit exists in the “current equivalent” string. I’ve tried FIND( ) and SEARCH( ) without success (screenshot attached).

Is there a wild card character to help find a value inside a string?
OR can you think of another way for me to tackle this??

Thanks!

[W_Vann_Hall]

You’re almost there.

The reason your sample formula fails is that in record 3,

FIND("SITHFAB002",{Current Equivalent})

is equal to 13, and 13 <> 1. Change your test from “= 1” to “>= 1”, and you’ll correctly match your test string anywhere within the target.

[Myles_Kendall1]

I see… I had assumed the 1 was a TRUE result of the FIND. Thanks @W_Vann_Hall

It’s working better now, but still not quite right. It seems to only detect the LAST unit code in the ‘Current Equivalent’ string.

Please see record 160 in the attached screenshot. That student has 4 ‘Awarded Units’. Thanks to the formula update, she has a :white_check_mark: for SITXFSA001 but I’ve added the :x: where the formula has failed to detect the unit out of the ‘Current Equivalent’ string.

Current formula is:
IF(FIND(“SITHFAB002”,{Current Equivalent},0)>=1,“ :white_check_mark: ”,"")

What else can we do to find that partial string??

[Myles_Kendall1]

I have stumbled across the solution for this… if anyone can explain it to me I would appreciate it.

The formula: FIND(“SITHFAB002”,{Current Equivalent},0)
…was only returning the correct result if SITHFAB002 was the last value in the string.

By changing the [startfromposition] from 0 to 1, ie: FIND(“SITHFAB002”,{Current Equivalent},1)
…I get the result I am looking for.

Can someone please enlighten me, why that makes all the difference?? :flushed:
Thanks.

[Andy_Lin1]

Going off of the reference ( https://support.airtable.com/hc/en-us/articles/203255215-Formula-Field-Reference ), they don’t explain how the [startFromPosition] value works. But as you’ve discovered, “1” is the first position, and “0” is the last position.
Note that the [startFromPosition] value is entirely optional. So, unless you’re looking for the value “SITHFAB002” at a particularly point in the {Current Equivalent} field, you can leave it out entirely.

Thus, your formula could be simplified to:
IF(FIND(“SITHFAB002”,{Current Equivalent})>=1," :white_check_mark: ","")

[Myles_Kendall1]

Thanks @Andy_Lin1
You’re right. [startFromPosition] is optional but, if left out, it defaults to “0” which is the last position, as you said.
Seems counter intuitive to me to ignore all of the preceding values by default. Lesson learnt.

• Another thing to be conscious of, in a direct input field the [startFromPosition] refers to the character position, but in a lookup field with a string referring to multiple values, the [startFromPosition] is looking at the value position in comma separated values.

• ie. With direct input value: SITHGAM001,SITHFAB002 - [startFromPosition] of “SITHFAB002” = 12
  With a lookup result: SITHGAM001,SITHFAB002 - [startFromPosition] of “SITHFAB002” = 2

[Andy_Lin1]

Related to your observation about the variation in FIND results between strings and arrays; you can use ARRAYJOIN to convert the lookup result into a string, which will return a value similar to a regular input field. It might be useful if you ever have to do a partial match.

[Sam_Blackman]

@Myles_Kendall1’s comment is really helpful at explaining which position [startFromPosition] refers to.

It would help a lot of users if Airtable updated its formula field reference documentation to make mention of when [startFromPosition] refers to the character position vs value position.

It would also be great if the docs for FIND() were updated with more examples with different input permutations, e.g.:

• an existent stringToFind, a single value whereToSearch reference, no [startFromPosition]
• an existent stringToFind, a single value whereToSearch reference, some value in [startFromPosition]
• an existent stringToFind, a multi-value whereToSearch reference, no [startFromPosition]
• an existent stringToFind, a multi-value whereToSearch reference, some value in [startFromPosition]

Then, it would be useful to have examples of those same permutations with stringToFind values that return 0/false (either because the string doesn’t exist or because it was prior to the start position).

[Brian_Bolduc]

My understanding is that the find() will search strings, not arrays. Given the above situation, find() will look through the first string it receives (i.e. the first string in an array of strings), not every string in the array. As Andy_Lin1 mentioned, converting the array into 1 long string will allow find() to essentially search the entire array and return the position (in the long string) if the search string is found, and 0 if the search string is not found.