Manage Long Formulas

[awalle]

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 ).

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???


Thanks in advance.
AWalle

 

[Asala42]

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]

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]

I'am sorry folks there was an error in the formula, I missed one part of it.

Sorry I already correct it.

Thanks