extracting numbers

[Guest]

i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

 

[David McRitchie]

Assuming that you have one digit in front of cash separated by a space
a single digit followed by stock --- so how does vice versa fit in.

a1: 3cash 5 stock
b1: =left(A1,1) ---- the length of 1 is optional for LEFT Worksheet Function
c1: =mid(a1,7,1)

or if you want numbers
b1: =value(left(a1,1))
c1: =value(mid(a1,7,1))

--

 

[Biff]

Hi!

Enter this formula in A2:

=IF(ISERROR(LEFT(A1)*1),"",LEFT(A1)*1)

Enter this formula in A3:

=IF(ISERROR(MID(A1,FIND(" ",A1)+1,1)*1),"",MID(A1,FIND(" ",A1)+1,1)*1)

Biff

 

[Ron Rosenfeld]

i need a formula to extract numbers.

if cell a1 is "3cash 5stock", i want to extract the number "3" to cell a2
and extract the number "5" to cell a3. the number in cash stock can vary
between 1 to 8 and vice versa.

can some help

If the values are always in that format, then:

a2: =LEFT(A1,FIND("cash",A1)-1)
A3: =MID(A1,FIND("cash",A1)+5,FIND("stock",A1)-FIND("cash",A1)-5)


--ron

 

[Guest]

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

 

[Guest]

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

 

[Biff]

Hi!

Is it possible to have entries like these:

cash 4stock
5cash stock
stock cash

I see in your earlier post this is for a timesheet. I would highly recommend
you redesign things so that you don't have to use such "hacked" formulas to
account for time worked. Things would be much easier if you enter hours
worked in one cell and the dept in another cell.

Biff

 

[David McRitchie]

Did you look at Biff's answer which pointed you back to his original answer, depending
on what you want you may want the parts in separated cells, when you posted your same
response earlier. If you can't figure it out you will have to be more specific about what
have in Column A and what you want in Column B as the answer or in Cols B & C as the answers.

 

[Guest]

Did you look at Biff's answer which pointed you back to his original answer, depending
on what you want you may want the parts in separated cells, when you posted your same
response earlier. If you can't figure it out you will have to be more specific about what
have in Column A and what you want in Column B as the answer or in Cols B & C as the answers.

is it possible for me to enter mulptiple formulas in one column?

somedays i need people to work 8 hours cash and stock and for them to work
cash for the first 3 hours and 4 hours stock after. Certain days i need them
to work to work stock for the first 4 hours and 3 hours cash after.

if column A is 3cash 4stock 4stock 4cash 6cash
5stock
(cash hrs)B would = 3 4 8
0
(stockhrs)C would = 4 4 6
5

i don't think this can be done, i have 8 differrent depts and i want each
dept column to extract their hours. like cash would extract its hours from
column A if there is a cash shift in it, like the example above.

 

[Guest]

try this in your cash column
=IF(ISERROR(SEARCH("cash",$A1)),0,IF(SEARCH("cash",$A1)=1,8,IF(AND(SEARCH("cash",$A1)=2,LEN($A1)<6),LEFT($A1,1),MID($A1,SEARCH("cash",$A1,1)-1,1))))
and this in your stock column
=IF(ISERROR(SEARCH("stock",A1)),0,IF(SEARCH("stock",A1)=1,8,IF(AND(SEARCH("stock",A1)=2,LEN(A1)<=6),LEFT(A1,1),MID(A1,SEARCH("stock",A1,1)-1,1))))

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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

 

[Guest]

hi Ron

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

 

[Ron Rosenfeld]

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

 

[Guest]

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]

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

 

[Guest]

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),LEFT(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]

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

 

[Guest]

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?