Thread: Two conditional formulas in one cell

Hi,

Thanks in advance for your help!

I am trying to write a formula so that:

if J9<10, display 00000
if J9 is between 10 and 99, display 0000
if J9 is 100-999, display 000
if J9 is 1000-9999, display 00
if J9 is 10000-99999, display 0
if J9>99999, the cell is blank (Note, it will be okay if this last one is not possible)

So far I have IF(AND(J9>9,J9<100),"0000","000")

So it works for numbers between 10-999 right now.

Can anyone tell me how to do this? I really appreciate your time and help!

Try this:

=IF(J9<10,"00000",IF(AND(10<=J9,J9<=99),"0000",IF(AND(100<=J9,J9<=999),"000",IF(AND(1000<=J9,J9<=9999),"00",IF(AND(10000<=J9,J9<=99999),"0","")))))

HTH

Try:

=LOOKUP(J9,{0,10,100,1000,10000,100000},{"00000","0000","000","00","0",""})

=choose(len(j9),"00000","0000","000","00","0","",)