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.
Best answer by kuovonne
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
)
+29Welcome 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
)
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
)
Thank so much - this worked brilliantly.
I am constantly amazed and the knowledge in this community.
+3Thank so much - this worked brilliantly.
I am constantly amazed and the knowledge in this community.
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
)Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.