Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 28, 2021 02:27 AM
Hi Everyone,
I am struggling to develop a formula to determine which position in an array another field is located. Here is my example:
I can use FIND to tell me the starting position of the string, but cannot figure out to determine which position Trans# is.
Thanks so much in advance.
Solved! Go to Solution.
Feb 28, 2021 07:52 AM
Welcome to the Airtable community!
Do you want something like this?
It isn’t clear if your array is a text string, a lookup, or a rollup.
If your array isn’t a text string, you will need to convert it to one first. The exact method depends on the field types involved.
Here is a formula where both the “array” and the string to find are text fields. If either is a different field type, this formula will not work.
On the other hand, I am not sure what your use case is, and I wonder if there is some better solution that does not involve getting the exact position.
IF(
AND(
{Element},
FIND(" " & {Element} & ",", " " & {Array As String} & ",")
),
LEN(
LEFT(" " & {Array As String} & ",", FIND(" " & {Element} & ",", " " & {Array As String} & ","))
) -
LEN(
SUBSTITUTE(
LEFT(" " & {Array As String} & ",", FIND(" " & {Element} & ",", " " & {Array As String} & ",")),
",",
""
)
) + 1
)
Feb 28, 2021 07:52 AM
Welcome to the Airtable community!
Do you want something like this?
It isn’t clear if your array is a text string, a lookup, or a rollup.
If your array isn’t a text string, you will need to convert it to one first. The exact method depends on the field types involved.
Here is a formula where both the “array” and the string to find are text fields. If either is a different field type, this formula will not work.
On the other hand, I am not sure what your use case is, and I wonder if there is some better solution that does not involve getting the exact position.
IF(
AND(
{Element},
FIND(" " & {Element} & ",", " " & {Array As String} & ",")
),
LEN(
LEFT(" " & {Array As String} & ",", FIND(" " & {Element} & ",", " " & {Array As String} & ","))
) -
LEN(
SUBSTITUTE(
LEFT(" " & {Array As String} & ",", FIND(" " & {Element} & ",", " " & {Array As String} & ",")),
",",
""
)
) + 1
)
Mar 01, 2021 05:38 AM
Thank so much - this worked brilliantly.
I am constantly amazed and the knowledge in this community.
Jun 30, 2023 09:32 AM
If you want to use Array Formulas, you can use the trick to change number to text in your Array Lookups
ARRAYJOIN(values,", ")&""
Then you can just remove all the extraneous quotations from above:
IF(
AND(
{Element},
FIND({Element},{Array As String})
),
LEN(
LEFT({Array As String}, FIND({Element},{Array As String}))
) -
LEN(
SUBSTITUTE(
LEFT({Array As String}, FIND({Element},{Array As String})),
",",
""
)
) + 1
)