If, And,Or

[nkunni]

Dear all,

Attached here a excel file,
I try with IF, AND, OR formula,
I cant explain the matter in english

 

[chirayu]

=IF(LEN(F15)=4,"GHJ1500"&F15,IF(LEN(F15)=3,"GHJ15000"&F15,IF(LEN(F15)=2,"GHJ150000"&F15,IF(LEN(F15)=1,"GHJ1500000"&F15,"Error"))))

 

[nkunni]

=IF(LEN(F15)=4,"GHJ1500"&F15,IF(LEN(F15)=3,"GHJ15000"&F15,IF(LEN(F15)=2,"GHJ150000"&F15,IF(LEN(F15)=1,"GHJ1500000"&F15,"Error"))))

This formula works perfectly,
Special thanks to understands my
- not explained excel sheet-

 

[GraH - Guido]

what about this:
="GHJ15"&TEXT(C15,"000000")
Seems to return the wanted result as well.

 

[chirayu]

@GraH - Guido

so does this lol but I just went with an IF earlier cos thread said IF

=LEFT("GHJ15000000",LEN("GHJ15000000")-LEN(F15))&F15

 

[GraH - Guido]

Yes! And we can go on with much more stuff like that, that's true.
But thread was not that clear on "requirement", so offered what I thought to be the most simple alternative without IF/AND/OR.
Since we are having a laugh:
=CHOOSE(LEN(F15),"GHJ1500000","GHJ150000","GHJ15000","GHJ1500")&F15

 

[chirayu]

@GraH - Guido

another one

="GHJ1500"&LOOKUP(LEN(F15),{1,2,3,4},{"000","00","0",""})&F15

 

[NARAYANK991]

Hi ,

Simplest is always best.

Following on from Guido's approach , the TEXT function can also be dispensed with , as in :

="GHJ" & (15000000 + F15)

Narayan

 

[bosco_yip]

Or,

=C15

>> Custom Cell Format, enter: "GHJ"15000000

all copied down

Regards
Bosco

 

[shrivallabha]

Beauty of Excel.
=REPLACE("GHJ000000",10-LEN(F15),LEN(F15),F15)

 

[chirayu]

=MID("GHJ15000000",1,LEN("GHJ15000000")-LEN(F15))&F15

 

[GraH - Guido]

Or,

=C15

>> Custom Cell Format, enter: "GHJ"15000000

all copied down

Regards
Bosco

Or just apply this custom format on the cell C15.

Beauty of Excel.
=REPLACE("GHJ000000",10-LEN(F15),LEN(F15),F15)

Don't you just love Excel for offering all these possibilities?

 

[nkunni]

Dear all,

Thanks,......... thanks a lot.

I just ask a flower, but God give me a "Garden"