ExcelBanter - Function error

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Function error (https://www.excelbanter.com/excel-worksheet-functions/218473-function-error.html)

The below formula works, but when I add TRIM( at the * and, ) at **, I get
the message "The formula you typed contains an error." Please help.

=IF(AND(ISBLANK('Paste USAL Cheque Data - temp'!B9),ISBLANK('Paste USAL
Cheque Data - temp'!B8)),"",IF(MID(TRIM('Paste USAL Cheque Data -
temp'!C9),1,1)="F",TRIM(PROPER(VLOOKUP(L2,'Debtor
Names'!$A$2:$B$65536,2,FALSE))),IF(MID(TRIM('Paste USAL Cheque Data -
temp'!C9),1,1)="M","USAL"&'Paste USAL Cheque Data - temp'!B9&"
"&VALUE(MID(TRIM('Paste USAL Cheque Data - temp'!C9),2,6))&"
"&TRIM(PROPER(VLOOKUP(VALUE(MID(*'Paste USAL Cheque Data -
temp'!C9**,2,6)),'Debtor Names'!$A$2:$B$65536,2,FALSE))),'Paste USAL Cheque
Data - temp'!$B$4&" "&'Paste USAL Cheque Data - temp'!$A$6&'Paste USAL
Cheque Data - temp'!$B$6)))

Too many nested functions. You need to break the formula down into separate
cells.

--
__________________________________
HTH

Bob

"nc" wrote in message
...
The below formula works, but when I add TRIM( at the * and, ) at **, I get
the message "The formula you typed contains an error." Please help.

=IF(AND(ISBLANK('Paste USAL Cheque Data - temp'!B9),ISBLANK('Paste USAL
Cheque Data - temp'!B8)),"",IF(MID(TRIM('Paste USAL Cheque Data -
temp'!C9),1,1)="F",TRIM(PROPER(VLOOKUP(L2,'Debtor
Names'!$A$2:$B$65536,2,FALSE))),IF(MID(TRIM('Paste USAL Cheque Data -
temp'!C9),1,1)="M","USAL"&'Paste USAL Cheque Data - temp'!B9&"
"&VALUE(MID(TRIM('Paste USAL Cheque Data - temp'!C9),2,6))&"
"&TRIM(PROPER(VLOOKUP(VALUE(MID(*'Paste USAL Cheque Data -
temp'!C9**,2,6)),'Debtor Names'!$A$2:$B$65536,2,FALSE))),'Paste USAL
Cheque
Data - temp'!$B$4&" "&'Paste USAL Cheque Data - temp'!$A$6&'Paste USAL
Cheque Data - temp'!$B$6)))