MATCH a partial string


#1

I started with help from this support topic:

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!


#2

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.


#3

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


#4

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.


#5

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


#6

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


#7

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.