Forum Discussion

lfreeman25's avatar
lfreeman25
Copper Contributor
Apr 17, 2019

Formula Help

I need to find out how to use dashes/hyphens in an IF formula without Excel trying to treat them like a minus sign. Ex: if cell A1 says "blue", then I want cell A2 to say "799-69999-1" (exactly like t...
excel
Formulas and Functions
Dutchman54's avatar
Dutchman54
Copper Contributor
Apr 17, 2019

lfreeman25 

 

Because you can only use a maximum of 7 layers  in a nested IF statement, below is a tip from John Walkenbach:

How to use more than 7 Nested IF statements:

Source: John Walkenbach

Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),

IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),

IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.

And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

   =IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")

    &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")

   &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")

   &IF(A1="J",10,"")

This method is not limited to 30 comparisons.

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 17, 2019

    Dutchman54 , limit of 7 layers is for Excel 2003 and below, starting from 2007 the limit is 64. Modern IFS allows up to 127 condition.

     

    However, in this case better to have helper table which maps one on another and use VLOOKUP, LOOKUP or INDEX/MATCH.

     

    When it'll be no side effect when

    =IF(1,788-88888-1,)

    where IF interprets 788-88888-1 as =788-88888-1 and returns -88101.

    If to hardcode when

    =IF(1,"788-88888-1",)

     

Resources