what's wrong with this formula?

[brockk]

Hi all, can anyone tell me what's wrong with the syntax of the following formula:

CELL E4: =IF(H4="","",IF(LEN(H4)<2,"MH-09-10-00"&H4),IF(LEN(H4)=2,"MH-09-10-0"&H4,"MH-09-10-"&H4)


what i am trying to accomplish is, depending on the length (digits) of cell h4 is what is going to determine which actual format I need to show up in E4. I keep getting a missing parenthesis or comma message......but really don't know where. Any help is appreciated.

the format I need is "MH-09-10-###" (------ ### ---> it is 3 digits long)

p.d. if there's a way that this formula can be simplified I would appreciate that too.

 

[Andrew Fergus]

Thye syntax for your original formula should be:
=IF(H4="","",IF(LEN(H4)<2,"MH-09-10-00"&H4,IF(LEN(H4)=2,"MH-09-10-0"&H4,"MH-09-10-"&H4)))

Notice you need a closing bracket for each if statement.

A slightly shorter version is:
=IF(H4="","","MH-09-10-" & IF(LEN(H4)<2,"00",IF(LEN(H4)=2,"0"&H4,""))&H4)

I'm sure this could be simplified further.......

Andrew

P.S. With your edit I see you are using values, so try this:
=IF(LEN(H10)=0,"","MH-09-10-"&TEXT(H10,"000"))

 

[Aladin Akyurek]

Does this satisfy?...

=IF(H4="","","MH-09-10-"&LOOKUP(LEN(H4),{0,"00";2,"0";3,""})&H4)

 

[brockk]

Thanks Aladin & Andrew for your kind suggestions........ It worked like a charm!