Hello all,
I am new to this forum. Please forgive any newbie issues I may have.

I am using this formula to find values from other columns, parsing out some things, and then cocatenating to create a new value. When the formula comes upon TEXT field containing '01E1, it decides it should be = 10. 10 is a nice number an all, but not what I want. the result should be like "POOL 04 - PSB_01E1", and then my formula uses that value to look up another value in another table.

I have tried adding the text function, with various formats (0,#,?), and different formats of the cell containing the "01E1", to no avail. I am hungry for clues on how to resolve. Here is the formula:

=IF(AND(tbl_All_Loan_Fields[[#This Row],[lsop_Indicator]]="Y",
tbl_All_Loan_Fields[[#This Row],[lsop_PoolID]]<>"not SOP pool"),
VLOOKUP(VLOOKUP("POOL "&tbl_All_Loan_Fields[[#This Row],[lls_LossSharePool]]&" - "&LEFT(tbl_All_Loan_Fields[[#This Row],[lsop_PoolID]],3)&"_"&tbl_All_Loan_Fields[[#This Row],[lcc_FedCallCdRCC]],LDC_SOP_POOL!$D$6:$Y$57,1,0),LDC_SOP_POOL!$D$6:$Y$57,22,0),0)

Outside of the spreadsheet, this formula appears to work:
=CONCATENATE("POOL"," ",Sheet1!F20," - ",LEFT(Sheet1!G20,3),"_",Sheet1!H20), which did not convert my 01E1 to 10.

I am using a considerable size binary workbook (35m), with about 20 worksheets, calculating 66 different values in a table. Windows 7 64 bit.

Let me know if further info is needed.