Run Out Room On If Statements

Hi,

I can't add any more If statements to the following

=IF(Z7=10,MID(W7,2,8),IF(LEFT(W7,3)="978",MID(W7,5,8),IF(LEFT(W7,4)="7153",MID(W7,1,8),IF(LEFT(W7,3)="486",MID(W7,1,8),IF(LEFT(W7,3)="276",MID(W7,1,8),IF(LEFT(W7,3)="891",MID(W7,1,8),IF(LEFT(W7,3)="873",MID(W7,1,8))))))))

I have half a dozen more to do can someone advise how to get around this please?

Many thanks.

Try this:

=IF(Z7=10,MID(W7,2,8),IF(LEFT(W7,3)="978",MID(W7,5,8),IF(or(LEFT(W7,4)="7153",LEFT(W7,3) ="486",LEFT(W7,3)="276",LEFT(W7,3)="891",LEFT(W7,3)="87 3"),MID(W7,1,8),""))) & IF ...

You can add more conditions beyond this.

Hope this helps.

Pete

Excel 2003 has a limit of 7 nested IF()'s so you're going to have to go with a lookup table.

Originally Posted by Cutter

Excel 2003 has a limit of 7 nested IF()'s ...

You can get around it like this:

=IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"") & IF( , ,"")

and so on.

I simplified the original expression as five of the conditions were all using MID(W7,1,8) and so could be put in one IF with an OR.

Pete