extracting numbers

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

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

On Sun, 5 Feb 2006 14:19:27 -0800, "cj" wrote:

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?

Yes there is.

But I don't know what you mean.

A2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE) & " office"
A3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE) & " inventory"

?????

--ron

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.

"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 14:19:27 -0800, "cj" wrote:

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?

Yes there is.

But I don't know what you mean.

A2: =REGEX.MID(A1,"\d+(?=\s*cash)",,FALSE) & " office"
A3: =REGEX.MID(A1,"\d+(?=\s*stock)",,FALSE) & " inventory"

?????

--ron

On Sun, 5 Feb 2006 18:16:27 -0800, "cj" wrote:

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.


Ah, one of the nice things about regular expressions is that this sort of
modification is easy:

A2: =REGEX.MID(A1,"\d+(?=\s*(cash|office))",,FALSE)
A3: =REGEX.MID(A1,"\d+(?=\s*(stock|inventory))",,FALSE )

Note the portion of the phrase (cash|office)

The pipe | means use either cash or office.
That they are enclosed in parenthesis properly groups them together.

The first function will now extract a number that is followed by zero or more
spaces and then either the word cash or the word office.


--ron

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8


"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 18:16:27 -0800, "cj" wrote:

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.


Ah, one of the nice things about regular expressions is that this sort of
modification is easy:

A2: =REGEX.MID(A1,"\d+(?=\s*(cash|office))",,FALSE)
A3: =REGEX.MID(A1,"\d+(?=\s*(stock|inventory))",,FALSE )

Note the portion of the phrase (cash|office)

The pipe | means use either cash or office.
That they are enclosed in parenthesis properly groups them together.

The first function will now extract a number that is followed by zero or more
spaces and then either the word cash or the word office.


--ron

On Sun, 5 Feb 2006 19:26:28 -0800, "cj" wrote:

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8

Try this:

=EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+"))

First we add a zero to the end of the string. You'll see why in a minute.

Now your phrases consist of digits followed by characters that are NOT digits.
The regex "{\D+)" says replace sets of characters that are not Digits with a
"+". The string of "not digits" can be 1 to many characters long.

So with "4train 3cash 1stock", after the substitutions, we have 4+3+1+0.

The EVAL function is also from morefunc.xll. I've not used it before but it
"Evaluates a formula or expression that is in the form of text and returns the
result."

So we've constructed the text and then apply the EVAL function to get the
result you want.

Let me know if it works out OK.


--ron

it didn't work, it can back with #value error.

=IF(ISERROR(SEARCH("cash",a1)),0,IF(SEARCH("cash", a1)=1,8,IF(AND(SEARCH("cash",a1)=2,LEN(a1)=6),LEF T(a1,1),MID(a1,SEARCH("cash",a1,1)-1,1))))

is there any way i can add to the above formula to extract numbers from cash
or office or stock?

"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 19:26:28 -0800, "cj" wrote:

thanks ron for your answers

how do i expand the formula to extract and add numbers in different
combinations and order?
eg:
a1: "3cash" "4stock" "5train" "3cash 4stock" "4train 3cash 1stock"

a2: 3 4 5 7
8

Try this:

=EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+"))

First we add a zero to the end of the string. You'll see why in a minute.

Now your phrases consist of digits followed by characters that are NOT digits.
The regex "{\D+)" says replace sets of characters that are not Digits with a
"+". The string of "not digits" can be 1 to many characters long.

So with "4train 3cash 1stock", after the substitutions, we have 4+3+1+0.

The EVAL function is also from morefunc.xll. I've not used it before but it
"Evaluates a formula or expression that is in the form of text and returns the
result."

So we've constructed the text and then apply the EVAL function to get the
result you want.

Let me know if it works out OK.


--ron

On Sun, 5 Feb 2006 21:23:27 -0800, "cj" wrote:

it didn't work, it can back with #value error.

Then either the data is not as you've posted it, or you made a typo in the
formula.

Post back with an exact copy of the data you used, and an exact copy of the
formula you used.


--ron

oops, didn't install morefunc.
it works now, thanks.
is there any way i can include the words cash|office|stock to make a2 add up
the numbers only if any of those 3 dept are entered in a1?


"Ron Rosenfeld" wrote:

On Sun, 5 Feb 2006 21:23:27 -0800, "cj" wrote:

it didn't work, it can back with #value error.

Then either the data is not as you've posted it, or you made a typo in the
formula.

Post back with an exact copy of the data you used, and an exact copy of the
formula you used.


--ron