Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Finding the position of a string in an array

Topic Labels: Formulas
Solved
Jump to Solution
977 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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

2 Replies 2

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.