IF(LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))>7, “ :x: ”, “ :white_check_mark: ” - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’))
LEN({Tags}) - LEN(SUBSTITUTE({Tags}, ‘,’, ‘’)))
Best answer by Justin_Barrett
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:
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?
+5Your 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:
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:
I LOVE YOU THANKS so MUCH!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
