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
Nov 21, 2019 11:22 AM
IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))>7, “ :x: ”, “ :white_check_mark: ” - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))
LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’)))
Solved! Go to Solution.
Nov 22, 2019 04:27 AM
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)))
Re: your second question, here’s a thread that shows how to extract a URL from an attachment field:
Nov 21, 2019 11:23 AM
& " " & returns only an NaN
Nov 21, 2019 01:27 PM
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?
Nov 22, 2019 04:27 AM
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)))
Re: your second question, here’s a thread that shows how to extract a URL from an attachment field:
Nov 23, 2019 02:21 AM
I LOVE YOU THANKS so MUCH!