+ Start a Discussion
KimberlyJKimberlyJ 

Function CASE formula

Hello,

I am trying to use a function CASE formula which will check for letters and replace them with a 0, add a leading 0 if only 6 characters and removes the dash.

 

ie: 81A-002 would change to 0810002

 

Please see formula below.  I have added the SQL equivalent below as well.

 

CASE(ISNUMBER(MID('0' + SUBSTITUTE(Name, '-', ''), 5, 1)),1,
('0' + SUBSTITUTE(Name, '-', '')),0,MID('0' + SUBSTITUTE(Name, '-', ''), 1, 4) + '0' + MID('0' + SUBSTITUTE(Name, '-', ''), 6, 2))

 

Syntax Error: Incorrect number of parameters for function 'CASE()'. Expected 4, received 5

 

 

CASE WHEN ISNUMERIC(SUBSTRING('0' + REPLACE(Name, '-', ''), 5, 1)) = 1 THEN '0' + REPLACE(Name, '-', '')

     ELSE SUBSTRING('0' + REPLACE(Name, '-', ''), 1, 4) + '0' + SUBSTRING('0' + REPLACE(Name, '-', ''), 6, 2)

     END

 

Thank you!

Best Answer chosen by Admin (Salesforce Developers) 
Shannon HaleShannon Hale

This is not the most elegant solution, but it seems to work:

 

LPAD(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              SUBSTITUTE(
                SUBSTITUTE(
                  SUBSTITUTE(
                    SUBSTITUTE(
                      SUBSTITUTE(
                        SUBSTITUTE(
                          SUBSTITUTE(
                            SUBSTITUTE(
                              SUBSTITUTE(
                                SUBSTITUTE(
                                  SUBSTITUTE(
                                    SUBSTITUTE(
                                      SUBSTITUTE(
                                        SUBSTITUTE(
                                          SUBSTITUTE(
                                            SUBSTITUTE(
                                              SUBSTITUTE(
                                                SUBSTITUTE(
                                                  SUBSTITUTE(
                                                    SUBSTITUTE(
                                                      SUBSTITUTE( LOWER( Name ), "z", "0" ),
                                                      "y",
                                                      "0"
                                                    ),
                                                    "x",
                                                    "0"
                                                  ),
                                                  "w",
                                                  "0"
                                                ),
                                                "v",
                                                "0"
                                              ),
                                              "u",
                                              "0"
                                            ),
                                            "t",
                                            "0"
                                          ),
                                          "s",
                                          "0"
                                        ),
                                        "r",
                                        "0"
                                      ),
                                      "q",
                                      "0"
                                    ),
                                    "p",
                                    "0"
                                  ),
                                  "o",
                                  "0"
                                ),
                                "n",
                                "0"
                              ),
                              "m",
                              "0"
                            ),
                            "l",
                            "0"
                          ),
                          "k",
                          "0"
                        ),
                        "j",
                        "0"
                      ),
                      "i",
                      "0"
                    ),
                    "h",
                    "0"
                  ),
                  "g",
                  "0"
                ),
                "f",
                "0"
              ),
              "e",
              "0"
            ),
            "d",
            "0"
          ),
          "c",
          "0"
        ),
        "b",
        "0"
      ),
    "a",
    "0"
    ),
    "-",
    ""
  ),
  7,
  "0"
)

 

Basically, it first converts any characters to lower case (because SUBSTITUTE() is case sensitive), and then uses nested SUBSTITUTE() functions to replace any "a" with "0", then "b", then "c", all the way down to "z". Then it removes the hyphen.

 

It will work even if there are multiple letters (e.g. 81A-0A2 or 96B-0H1)

 

I had to put the "z" SUBSTITUTE() call all on the same line because all the indenting spaces put the formula over the 3,900 character limit :-)

 

All Answers

Shannon HaleShannon Hale

I don't think you need a CASE() function here -- I haven't had a chance to dig into the subsitution part for the letter but I started to stub it out like this:

 

LPAD( /* left pad the final string as needed */
  SUBSTITUTE( /* This will remove the hyphen, if any */
/* TODO - this is where to replace any letters with 0 */,
"-", /* find the hyphen */ "" /* replace the hyphen */ ), 7, /* number of characters to pad to */ "0" /* character to pad with */ )

 

Is the letter always in the same position, preceding the hyphen?

 

KimberlyJKimberlyJ

LPAD(
SUBSTITUTE(

Name,

"-",
""
),
7,
"0"
)

 

So far this is working except it's not replacing the letter with a zero. And to answer your question, the letter is not always in the same place.

Thanks for your help! Can you advise on the letter problem we are having?

Shannon HaleShannon Hale

This is not the most elegant solution, but it seems to work:

 

LPAD(
  SUBSTITUTE(
    SUBSTITUTE(
      SUBSTITUTE(
        SUBSTITUTE(
          SUBSTITUTE(
            SUBSTITUTE(
              SUBSTITUTE(
                SUBSTITUTE(
                  SUBSTITUTE(
                    SUBSTITUTE(
                      SUBSTITUTE(
                        SUBSTITUTE(
                          SUBSTITUTE(
                            SUBSTITUTE(
                              SUBSTITUTE(
                                SUBSTITUTE(
                                  SUBSTITUTE(
                                    SUBSTITUTE(
                                      SUBSTITUTE(
                                        SUBSTITUTE(
                                          SUBSTITUTE(
                                            SUBSTITUTE(
                                              SUBSTITUTE(
                                                SUBSTITUTE(
                                                  SUBSTITUTE(
                                                    SUBSTITUTE(
                                                      SUBSTITUTE( LOWER( Name ), "z", "0" ),
                                                      "y",
                                                      "0"
                                                    ),
                                                    "x",
                                                    "0"
                                                  ),
                                                  "w",
                                                  "0"
                                                ),
                                                "v",
                                                "0"
                                              ),
                                              "u",
                                              "0"
                                            ),
                                            "t",
                                            "0"
                                          ),
                                          "s",
                                          "0"
                                        ),
                                        "r",
                                        "0"
                                      ),
                                      "q",
                                      "0"
                                    ),
                                    "p",
                                    "0"
                                  ),
                                  "o",
                                  "0"
                                ),
                                "n",
                                "0"
                              ),
                              "m",
                              "0"
                            ),
                            "l",
                            "0"
                          ),
                          "k",
                          "0"
                        ),
                        "j",
                        "0"
                      ),
                      "i",
                      "0"
                    ),
                    "h",
                    "0"
                  ),
                  "g",
                  "0"
                ),
                "f",
                "0"
              ),
              "e",
              "0"
            ),
            "d",
            "0"
          ),
          "c",
          "0"
        ),
        "b",
        "0"
      ),
    "a",
    "0"
    ),
    "-",
    ""
  ),
  7,
  "0"
)

 

Basically, it first converts any characters to lower case (because SUBSTITUTE() is case sensitive), and then uses nested SUBSTITUTE() functions to replace any "a" with "0", then "b", then "c", all the way down to "z". Then it removes the hyphen.

 

It will work even if there are multiple letters (e.g. 81A-0A2 or 96B-0H1)

 

I had to put the "z" SUBSTITUTE() call all on the same line because all the indenting spaces put the formula over the 3,900 character limit :-)

 

This was selected as the best answer
KimberlyJKimberlyJ

At first, thought you were being a smart alec.  Worked like a charm! I marked it as such. 

Thank you!