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
- Stacked IF statements to produce output based on c...

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

0
1170
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

Feb 24, 2023 10:48 AM

I managed to write a grammatically correct formula that I thought encompassed my query, but I'm getting a #ERROR! on the output.

My aim is to add a conditional statement to the formula @AirOps was kind enough to format for me -

SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13, "5", {US Price})), ".", "")

-where if {US Price} was less than 4 digits, it would add a 0 after the five, before the price. (Ex. if the price is 9.99, the output should end in 50999 instead of 5999.) This is what I came up with:

SUBSTITUTE(IF(ISBN13!="", CONCATENATE(ISBN13,"5",{US Price},".","") &

IF(LEN({US Price})<4, CONCATENATE(ISBN13,"5,","0",{US Price},".",""))))

The formula was accepted, but didn't produce an output. I assume I'm missing something, like maybe another substitution clause? Does anyone have any suggestions?

4 Replies 4

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

Feb 24, 2023 11:47 AM - edited Feb 24, 2023 12:46 PM

Hey @mchristo33!

I moved around a few things. Give this a shot.

(*update 2/24/2023: per this more recent post, this formula will not work if the referenced US Price field is a number field. Please refer to the formula found in the linked post to account for the difference in field types between integer and string data types*)

```
SUBSTITUTE(
SUBSTITUTE(
IF(
AND(
{ISBN13},
{US Price}
),
IF(
LEN({US Price}) <= 4,
CONCATENATE(
{ISBN13}, "5,", "0", {US Price}
),
CONCATENATE(
{ISBN13}, "5", {US Price}
)
),
IF(
{ISBN13},
CONCATENATE(
{ISBN13}, "5"
)
)
),
".",
""
),
",",
""
)
```

Here's what it looks like when I tested it:

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

Feb 24, 2023 12:17 PM - edited Feb 24, 2023 12:19 PM

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

Feb 24, 2023 12:43 PM

Ah, I see what's happening.

My original formula assumes that your *US Price* field returns a string instead of a number.

The error was being thrown from this line of the formula:

LEN({US Price}) <= 4

This happened because the LEN() function can only accept a string. So if your *US Price* field returns a number, then an error will be thrown.

We can implement a workaround by introducing an empty string value along with the field to the function parameter. Here's what it looks like:

LEN(SUBSTITUTE("" & {US Price}, ".", "")) < 4

You'll notice that I've added a nested SUBSTITUTE() function.

I was looking at your example and noticed that you used the value 9.99 as a reference for checking against four or more digits in the value. When I tested the value of 9.99, the formula was actually adding the zero character to the final string instead.

I realized that the decimal was being captured and included in the length of the string.

The character substitution will guarantee that we're only counting digits.

At the core structure of this snippet, we have this:

LEN("" & {US Price})

Since we add the empty string, we can force what's called type coercion. Despite being empty, the presence of the string will force the entire return value into a string instead of a number.

This should fix the error you're being thrown.

Since there doesn't appear to be any data type conflicts with the *ISBN13* field, we don't have to make any adjustments to it.

Here's the final formula:

```
SUBSTITUTE(
SUBSTITUTE(
IF(
AND(
{ISBN13},
{US Price}
),
IF(
LEN(SUBSTITUTE("" & {US Price}, ".", "")) < 4,
CONCATENATE(
{ISBN13}, "5,", "0", {US Price}
),
CONCATENATE(
{ISBN13}, "5", {US Price}
)
),
IF(
{ISBN13},
CONCATENATE(
{ISBN13}, "5"
)
)
),
".",
""
),
",",
""
)
```

Here's what it looks like if the *ISBN13* field is a number:

And here's the result if it's a string:

I'll update the formula snippet in my original post as well.

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

Feb 24, 2023 12:46 PM

Reply