• 461K Members
• 5,797 Online
• 559K Conversations

## Formula Help

Occasional Visitor

# 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 this with the hyphens). However, cell A2 is showing -59301. How do I make the end result in cell A2 say "799-69999-1"? I also want to find a formula that will allow me to input multiple criteria. Ex: if cell A1 says blue, then cell A2 should be 799-99999-1. If cell A1 says red, then cell A2 should be 788-88888-1, etc. it would be up to 10 different criteria. Any help would be appreciated!
2 Replies

# Re: Formula Help

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.

# Re: Formula Help

@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",)`

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies