Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Finding the position of a string in an array

Solved
Jump to Solution
1989 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Brian_Reeds
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi Everyone,

I am struggling to develop a formula to determine which position in an array another field is located. Here is my example:

  • The Array of Trans# is 2239, 4539, 4792, 8912, 10394
  • I am trying to find a formula that says Trans# 4792 is in position 3, Trans# is in position 1, Trans# 8912 is in position 4, etc.

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.

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Do you want something like this?

image

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
)

See Solution in Thread

3 Replies 3
kuovonne
18 - Pluto
18 - Pluto

Welcome to the Airtable community!

Do you want something like this?

image

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.

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
)