thanks guys both formulas work great. is there any way i can add in
"inventory" to the stock formula and "office" to the cash formula?

"Ron Rosenfeld" wrote:

On Sat, 4 Feb 2006 21:28:26 -0800, "cj" wrote:

thanks guys for the answer!

all 3 differrent formulas worked! i also need cell a2 to extract value of 8
if a1 is just "cash", value of 7 if a1 is "7cash",value of 6 if a1 is
"6cash", and so on and same for a3 to extract 8 if a1 is just "stock", etc.
and what if the order is reveresed from 3cash 4stock to 4stock 3cash?

plz help



You seem to be gradually adding conditions to your original request. It would
be best if you listed your full specifications initially.

Here is a method to extract a whole (integer) number preceding the particular
word.

In addition, there can be zero or several spaces between the number and the
word.

Also, the function is case insensitive.

1. Download and install Longre's free morefunc.xll add-in from

2.

a2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE)
a3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE)


--ron