MATCH a partial string

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.

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 for SITXFSA001 but I’ve added the 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,“ ”,"")

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

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??

Thanks.

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," ","")

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

  
  

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.

@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).

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.