On Thu, 05 Apr 2007 20:22:48 -0400, Ron Rosenfeld
wrote:

On Thu, 5 Apr 2007 11:20:05 -0700, 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?

Try this:

1. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

It can be embedded in the workbook if that is an issue.

Then use this **array** formula (enter with <ctrl<shift<enter)

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP).*?(?=\s|$)"))

where "rng" is the range that contains your strings.
--ron

This might be more foolproof:

=SUM(--REGEX.MID(rng&"GBP0","(?<=GBP)[\d\.]+"))


--ron