Manage Long Formulas

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
Hi, there

I know this has been post before, but I forgot how to do this simple task. (maybe my friends "Alz" and "heimer" come today with me :rolleyes: ).

I need to manage a long formula in a single Cell :

=if(trim(G2)<>"",IF(iserr(FIND("G",G2)),G2,IF(FIND("-",G2)>0,MID(G2,FIND("-",G2)-5,FIND(" ",G2,FIND("-",G2))-FIND("-",G2)+5)),IF(iserr(FIND("G",H2)),H2,IF(FIND("-",H2)>0,MID(H2,FIND("-",H2)-5,FIND(" ",H2,FIND("-",H2))-FIND("-",H2)+5)))))

Obviously the excel gave me a "Too many argument" error,

Can somebody refresh my memory??? :LOL:


Thanks in advance.
AWalle
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Asala42

Well-known Member
Joined
Feb 26, 2002
Messages
2,318
Is this what you're going for?

=IF(TRIM(G2)<>"",IF(ISERR(FIND("G",G2)),G2,IF(FIND("-",G2)>0,MID(G2,FIND("-",G2)-5,FIND(" ",G2,FIND("-",G2))-FIND("-",G2)+5))),IF(ISERR(FIND("G",H2)),H2,IF(FIND("-",H2)>0,MID(H2,FIND("-",H2)-5,FIND(" ",H2,FIND("-",H2))-FIND("-",H2)+5))))

It looks like you moved an argument over somewhere from the first IF

=if(trim(G2)<>"",IF(iserr(FIND("G",G2)),G2,IF(FIND("-",G2)>0,MID(G2,FIND("-",G2)-5,FIND(" ",G2,FIND("-",G2))-FIND("-",G2)+5)),IF(iserr(FIND("G",H2)),H2,IF(FIND("-",H2)>0,MID(H2,FIND("-",H2)-5,FIND(" ",H2,FIND("-",H2))-FIND("-",H2)+5)))))

<--the "if-false" argument is missing from the first IF statement. I kind of guessed what you wanted, but it looks like might have swept that argument inside one of the nested ifs.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
You seem to have one to many closing parenthises in your formula...

Try:

=IF(TRIM(G2)<>"",IF(ISERR(FIND("G",G2)),G2,IF(FIND("-",G2)>0,MID(G2,FIND("-",G2)-5,FIND(" ",G2,FIND("-",G2))-FIND("-",G2)+5),IF(ISERR(FIND("G",H2)),H2,IF(FIND("-",H2)>0,MID(H2,FIND("-",H2)-5,FIND(" ",H2,FIND("-",H2))-FIND("-",H2)+5))))),"")

Also, I added a Value-if-False qualifier of "" for the original condition IF(Trim(G2)<>"",... so that you will get a blank if that is false....

you might want to test all possibilities to ensure the formula is doing its job correctly.
 

awalle

Board Regular
Joined
Jul 12, 2006
Messages
56
I'am sorry folks there was an error in the formula, I missed one part of it.

Sorry I already correct it.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,113,809
Messages
5,544,415
Members
410,609
Latest member
agarci1096
Top