Help

Formular help needed basics and advanced

Topic Labels: Formulas
Solved
Jump to Solution
2475 4
cancel
Showing results for 
Search instead for 
Did you mean: 
airtableManuel
6 - Interface Innovator
6 - Interface Innovator
  1. max 7 words seperated with , - is functionable
    count the words- functionable
    now how can i combine both into a formular?

IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))>7, “ :x: ”, “ :white_check_mark: ” - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))

LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’)))

  1. Attachment fields:
    you say in the formular: attachment field then cames the full name and the link
    but i want ONLY THE link
    how can i do that?
1 Solution

Accepted Solutions
Justin_Barrett
18 - Pluto
18 - Pluto

Your first formula is missing some information. After a quick test, I’m guessing this is the actual formula you’re using that’s leading to the NaN output:

IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>7, '❌', '✅' & LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', '')))

The problem has to do with order of operations in that last section with the green check emoji, where you’re trying to concatenate the number of tags after the emoji. Airtable processes each piece of that section one by one from left to right. First there’s the emoji, which is a string. The next piece it finds is LEN({Tags}), which is being combined with the emoji using the & operator. This results in a string. So far so good. However, the last piece you’re processing—the length of the {Tags} field with commas removed—is being combined using the - (minus) mathematical operator. In essence, you’re telling Airtable to take a string and subtract a number, and it doesn’t know how to do that, hence the NaN error.

Thankfully the fix is pretty simple. You need to force the two LEN() functions to process first and create the final number, and then concatenate the result with the check emoji. This is done by wrapping parentheses around both LEN() functions, which gives you this:

IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>7, '❌', '✅' & (LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))))

However, that’s still not quite accurate. The math you’re doing finds the number of commas, which will actually be one less than the number of tags. For your initial comparison, the number should be 6, not 7, because 7 items will have 6 commas between them. To show an accurate count in the latter portion, you need to add 1 to that total. This also forces you to only run the main formula if there are tags to count, as no tags will still show a count of 1. With all that in mind, the real final formula is this:

IF({Tags}, IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>6, '❌', '✅' & (LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', '')) + 1)))

45%20AM

Re: your second question, here’s a thread that shows how to extract a URL from an attachment field:

See Solution in Thread

4 Replies 4
airtableManuel
6 - Interface Innovator
6 - Interface Innovator

& " " & returns only an NaN

Matthew_Moran
7 - App Architect
7 - App Architect

I cannot understand your question. Can you clarify or provide a screen shot of the data you are working with and the proposed/desired output?

Justin_Barrett
18 - Pluto
18 - Pluto

Your first formula is missing some information. After a quick test, I’m guessing this is the actual formula you’re using that’s leading to the NaN output:

IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>7, '❌', '✅' & LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', '')))

The problem has to do with order of operations in that last section with the green check emoji, where you’re trying to concatenate the number of tags after the emoji. Airtable processes each piece of that section one by one from left to right. First there’s the emoji, which is a string. The next piece it finds is LEN({Tags}), which is being combined with the emoji using the & operator. This results in a string. So far so good. However, the last piece you’re processing—the length of the {Tags} field with commas removed—is being combined using the - (minus) mathematical operator. In essence, you’re telling Airtable to take a string and subtract a number, and it doesn’t know how to do that, hence the NaN error.

Thankfully the fix is pretty simple. You need to force the two LEN() functions to process first and create the final number, and then concatenate the result with the check emoji. This is done by wrapping parentheses around both LEN() functions, which gives you this:

IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>7, '❌', '✅' & (LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))))

However, that’s still not quite accurate. The math you’re doing finds the number of commas, which will actually be one less than the number of tags. For your initial comparison, the number should be 6, not 7, because 7 items will have 6 commas between them. To show an accurate count in the latter portion, you need to add 1 to that total. This also forces you to only run the main formula if there are tags to count, as no tags will still show a count of 1. With all that in mind, the real final formula is this:

IF({Tags}, IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', ''))>6, '❌', '✅' & (LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ',', '')) + 1)))

45%20AM

Re: your second question, here’s a thread that shows how to extract a URL from an attachment field:

I LOVE YOU THANKS so MUCH!