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 decimal points

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Brian" wrote:

No....most of the other rows that have data don't have GBP in them.
Most of them are blank.....or have some other words....but not GBP

"Ron Coderre" wrote:

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
they don't have that GBP string in them.
Would that matter?

"Ron Coderre" wrote:

OK, Brian.....This is the latest in a series of final formulas :)

(ARRAY FORMULA)
=SUM(--IF(ISNUMBER(SEARCH("GBP",A2:A300)),MID(LEFT(A2:A30 0,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....
Here's the exact wording that's in one of my AL cells.

REMARK=/OCMT/USD772 27/ /BNF/GBP391.38 REFNO: 9443009139 B/O: AMERICAN
EXPRESS LTD

Would this alter the formula?

Thanks again!

Here's the

"Ron Coderre" wrote:

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 to search?
My data in column AL can range from row 2 to row 300.
How can I factor this into the forumla?

"Ron Coderre" wrote:

Try something like this:

=SUM(MID(LEFT(AL19,SEARCH("
B/O",AL19)-1),SEARCH("GBP",AL19)+3,255),MID(LEFT(AL50,SEARCH( "
B/O",AL50)-1),SEARCH("GBP",AL50)+3,255))


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"Brian" wrote:

I have multiple columns and rows of data.
In some of my rows, there will be data in column AL,that contains a line
like the following:
USD72990,48/ /BNF/GBP37238.14 B/O: AMEX

Is there a forumla I can use that will add up all the figures that follow
the "GBP" in column AL?

For example:
In cell AL 19 I have the following: USD72990,48/ /BNF/GBP37238.14 B/O: AMEX
In cell AL 50 I have the following: USD772,27/ /BNF/GBP391.38 B/O: AMEX

I'd like a formula that will give me the result : 37,629.52 (the addition of
the numbers following the GBP in column AL)

Any suggestions?