This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Airtable Community
- Discussions
- Ask A Question
- Formulas
- Formular help needed basics and advanced

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Topic Labels:
Formulas

Solved

Jump to Solution

0
1855
4

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Comment Post Options

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 21, 2019 11:22 AM

- 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}, ‘,’, ‘’)))

- 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?

Solved! Go to Solution.

1 Solution

Accepted Solutions

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Reply

4 Replies 4

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 21, 2019 11:23 AM

& " " & returns only an NaN

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 21, 2019 01:27 PM

Solved
See Solution in Thread

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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:

Reply

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Nov 23, 2019 02:21 AM

I LOVE YOU THANKS so MUCH!