Stacked IF statements to produce output based on character count of source string

[mchristo33]

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?

[Ben_Young1]

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:

[mchristo33]

Unfortunately, it's still returning #ERROR! for me. Based on what you got from your test, it should be right. Could it be the source string? The US Price is manually entered, formatted as currency, and the ISBN13 is an output from another formula. 

[Ben_Young1]

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.

[mchristo33]

It's probably going to take me some time to actually understand how it works, but it does work! Thank you so much.