what's wrong with this formula?

brockk

Board Regular
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.

Last edited:

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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"))

Last edited:
Does this satisfy?...

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

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

Replies
3
Views
205
Replies
4
Views
126
Replies
5
Views
203
Replies
1
Views
167
Replies
12
Views
580

1,206,971
Messages
6,075,925
Members
446,170
Latest member
zzzz02

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back