----- Stout Fireplug ----- Nashville/Tennessee, Globalfight Personals: SmyrnaBurly City: Nashville State/Country: Tennessee Age: 28 Taste: Bisexual Prefers: I would like to meet someone older than myself Short, stocky guy seeking guys my size or larger for workout, fun, and friendship. Pretty new to wrestling but very strong. If interested, hit me up.

The issue probably lies in the data. So far, I only get the #VALUE! error if the characters between "GBP" and " REFNO" contain letters or do not resolve to a valid number: Examples: These work.... GBP 100.00 REFNO GBP 00100.00 REFNO GBP100.00 REFNO These don't.... GBP a100.00 REFNO........"a" GBP 100.00REFNO...........no space before REFNO GBP 10.0.0.0 REFNO...........extra

Do other cells in Col_AL contain "GBP", but no "REFNO" or no amount between "GBP" and "REFNO"? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks again Ron....but still no luck. I tried the formula listed below (changed the A2:A300 to AL2:AL300) and still get the #VALUE! response. Some other rows have data in column AL....but I don't need any of these if

OK, Brian.....This is the latest in a series of final formulas :) (ARRAY FORMULA) =SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A300,SEARCH(" REFNO",A2:A300)-1),SEARCH("GBP",A2:A300)+3,255))) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: For some reason I got a #VALUE! response when I do the shift-cntrl-enter Maybe I should be more specific

No need for me to be lazy, right? Here's the formula, referencing AL2:AL300 =SUM(--IF(ISNUMBER(SEARCH("GBP",AL2:AL300)),MID(LEFT(AL2:AL300,SEARCH(" B/O",AL2:AL300)-1),SEARCH("GBP",AL2:AL300)+3,255))) ( remember to use [ctrl]+[shift]+[enter] ) Does that help? *********** Regards, Ron XL2002, WinXP "Brian" wrote: Thanks Ron....but is there a way I can put in a range of cells