+ Reply to Thread
Results 1 to 29 of 29

extracting numbers

  1. #1
    cj
    Guest

    extracting numbers

    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

  2. #2
    Biff
    Guest

    Re: extracting numbers

    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

    "cj" <[email protected]> wrote in message
    news:[email protected]...
    >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




  3. #3
    David McRitchie
    Guest

    Re: extracting numbers

    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))

    --
    ---
    HTH,
    David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
    My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
    Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

    "cj" <[email protected]> wrote ...
    > 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.




  4. #4
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sat, 4 Feb 2006 19:58:21 -0800, "cj" <[email protected]> wrote:

    >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

  5. #5
    cj
    Guest

    Re: extracting numbers

    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" wrote:

    > 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
    >
    > "cj" <[email protected]> wrote in message
    > news:[email protected]...
    > >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

    >
    >
    >


  6. #6
    cj
    Guest

    Re: extracting numbers

    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



    "Ron Rosenfeld" wrote:

    > On Sat, 4 Feb 2006 19:58:21 -0800, "cj" <[email protected]> wrote:
    >
    > >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
    >


  7. #7
    Biff
    Guest

    Re: extracting numbers

    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

    "cj" <[email protected]> wrote in message
    news:[email protected]...
    > 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" wrote:
    >
    >> 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
    >>
    >> "cj" <[email protected]> wrote in message
    >> news:[email protected]...
    >> >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

    >>
    >>
    >>




  8. #8
    David McRitchie
    Guest

    Re: extracting numbers

    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.



  9. #9
    cj
    Guest

    Re: extracting numbers



    "David McRitchie" wrote:

    > 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.

  10. #10
    paul
    Guest

    Re: extracting numbers

    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))))
    --
    paul
    remove nospam for email addy!



    "cj" wrote:

    >
    >
    > "David McRitchie" wrote:
    >
    > > 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.


  11. #11
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sat, 4 Feb 2006 21:28:26 -0800, "cj" <[email protected]> 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

  12. #12
    cj
    Guest

    Re: extracting numbers

    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" <[email protected]> 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
    >


  13. #13
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sun, 5 Feb 2006 14:19:27 -0800, "cj" <[email protected]> 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

  14. #14
    cj
    Guest

    Re: extracting numbers

    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" <[email protected]> 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
    >


  15. #15
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sun, 5 Feb 2006 18:16:27 -0800, "cj" <[email protected]> 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

  16. #16
    cj
    Guest

    Re: extracting numbers

    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" <[email protected]> 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
    >


  17. #17
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sun, 5 Feb 2006 19:26:28 -0800, "cj" <[email protected]> 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

  18. #18
    cj
    Guest

    Re: extracting numbers

    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" wrote:

    > On Sun, 5 Feb 2006 19:26:28 -0800, "cj" <[email protected]> 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
    >


  19. #19
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Sun, 5 Feb 2006 21:23:27 -0800, "cj" <[email protected]> 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

  20. #20
    cj
    Guest

    Re: extracting numbers

    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" <[email protected]> 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
    >


  21. #21
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Mon, 6 Feb 2006 17:18:28 -0800, "cj" <[email protected]> wrote:

    >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?


    Yes, there is.

    But do you have any more specifications?
    --ron

  22. #22
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld <[email protected]>
    wrote:

    >On Mon, 6 Feb 2006 17:18:28 -0800, "cj" <[email protected]> wrote:
    >
    >>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?

    >
    >Yes, there is.
    >
    >But do you have any more specifications?
    >--ron


    This is one way:

    =IF(REGEX.FIND(A1,"cash|office|stock")>0,EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+")),"")

    But it'll be easiest if you could lay out all of your specifications at once,
    for your project.


    --ron

  23. #23
    cj
    Guest

    Re: extracting numbers

    one last specification for the formula,if column a1 is "3cash 4office 1train"
    ,i want a2 which is the cash|office hrs column to add only the 3cash 4office
    is = 7 and a3 which is the train|hiring|other hrs column to add only the
    1train is = 1.
    i have 6 different hrs columns with 3 or more different depts.
    the order of depts can vary in order and combination.
    eg)

    a1 shift worked column a1= 3cash 4office 1other a1=3stock
    2train 2pay
    a2 cash|office|pay column a2= 7 a2=2
    a3 stock|train|rec column a3= 0 a3=5
    a4 other|front|ret|rel column a4=1 a4=0

    "Ron Rosenfeld" wrote:

    > On Mon, 06 Feb 2006 21:17:25 -0500, Ron Rosenfeld <[email protected]>
    > wrote:
    >
    > >On Mon, 6 Feb 2006 17:18:28 -0800, "cj" <[email protected]> wrote:
    > >
    > >>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?

    > >
    > >Yes, there is.
    > >
    > >But do you have any more specifications?
    > >--ron

    >
    > This is one way:
    >
    > =IF(REGEX.FIND(A1,"cash|office|stock")>0,EVAL(REGEX.SUBSTITUTE(A1&"0","(\D+)","+")),"")
    >
    > But it'll be easiest if you could lay out all of your specifications at once,
    > for your project.
    >
    >
    > --ron
    >


  24. #24
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Tue, 7 Feb 2006 12:59:29 -0800, "cj" <[email protected]> wrote:

    >one last specification for the formula,if column a1 is "3cash 4office 1train"
    >,i want a2 which is the cash|office hrs column to add only the 3cash 4office
    >is = 7 and a3 which is the train|hiring|other hrs column to add only the
    >1train is = 1.
    >i have 6 different hrs columns with 3 or more different depts.
    >the order of depts can vary in order and combination.
    >eg)
    >
    >a1 shift worked column a1= 3cash 4office 1other a1=3stock
    >2train 2pay
    >a2 cash|office|pay column a2= 7 a2=2
    >a3 stock|train|rec column a3= 0 a3=5
    >a4 other|front|ret|rel column a4=1 a4=0


    If this is all you've wanted, we've wasted a lot of time.

    In your first post, you indicated that the numbers would be 1-8.

    And you've always had the strings formatted with no space between the number
    and the description.

    All you need to do to pull out the number associated with a particular
    descriptor is:

    =MID(A1,FIND(descriptor,A1)-1,1) or, for the office:

    =MID(A1,FIND("office",A1)-1,1)

    And you can use the add operator to add these together.

    So if you wanted to add only the cash and office from a string

    "3cash 4office 1train"

    all you need to do is:

    =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)

    You should be able to figure out the rest by yourself.


    --ron

  25. #25
    cj
    Guest

    Re: extracting numbers

    hi ron

    thanks for your answers and patience, i really appreciate your help.
    i play with the formulas

    a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1,"stock|train|rec|st|tr")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))

    a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1,"cash|office|pay|cs|off|py")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))

    these 2 formulas work great: when i enter in a1 just "cash" a2 returns with
    8 and same for cash. thats what i want and i can add extra words if the shift
    is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine
    except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock",
    both a2 and a3 return with 8. its there any way can separate them. i need a2
    to return with 2 and a3 to return with 5?

    thanks

    "Ron Rosenfeld" wrote:

    > On Tue, 7 Feb 2006 12:59:29 -0800, "cj" <[email protected]> wrote:
    >
    > >one last specification for the formula,if column a1 is "3cash 4office 1train"
    > >,i want a2 which is the cash|office hrs column to add only the 3cash 4office
    > >is = 7 and a3 which is the train|hiring|other hrs column to add only the
    > >1train is = 1.
    > >i have 6 different hrs columns with 3 or more different depts.
    > >the order of depts can vary in order and combination.
    > >eg)
    > >
    > >a1 shift worked column a1= 3cash 4office 1other a1=3stock
    > >2train 2pay
    > >a2 cash|office|pay column a2= 7 a2=2
    > >a3 stock|train|rec column a3= 0 a3=5
    > >a4 other|front|ret|rel column a4=1 a4=0

    >
    > If this is all you've wanted, we've wasted a lot of time.
    >
    > In your first post, you indicated that the numbers would be 1-8.
    >
    > And you've always had the strings formatted with no space between the number
    > and the description.
    >
    > All you need to do to pull out the number associated with a particular
    > descriptor is:
    >
    > =MID(A1,FIND(descriptor,A1)-1,1) or, for the office:
    >
    > =MID(A1,FIND("office",A1)-1,1)
    >
    > And you can use the add operator to add these together.
    >
    > So if you wanted to add only the cash and office from a string
    >
    > "3cash 4office 1train"
    >
    > all you need to do is:
    >
    > =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
    >
    > You should be able to figure out the rest by yourself.
    >
    >
    > --ron
    >


  26. #26
    Biff
    Guest

    Re: extracting numbers

    >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.


    My suggestion still stands!

    Use *1* cell for hours worked and *1* cell for the dept.

    Biff

    "cj" <[email protected]> wrote in message
    news:[email protected]...
    > hi ron
    >
    > thanks for your answers and patience, i really appreciate your help.
    > i play with the formulas
    >
    > a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1,"stock|train|rec|st|tr")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    >
    > a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1,"cash|office|pay|cs|off|py")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    >
    > these 2 formulas work great: when i enter in a1 just "cash" a2 returns
    > with
    > 8 and same for cash. thats what i want and i can add extra words if the
    > shift
    > is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine
    > except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock",
    > both a2 and a3 return with 8. its there any way can separate them. i need
    > a2
    > to return with 2 and a3 to return with 5?
    >
    > thanks
    >
    > "Ron Rosenfeld" wrote:
    >
    >> On Tue, 7 Feb 2006 12:59:29 -0800, "cj" <[email protected]>
    >> wrote:
    >>
    >> >one last specification for the formula,if column a1 is "3cash 4office
    >> >1train"
    >> >,i want a2 which is the cash|office hrs column to add only the 3cash
    >> >4office
    >> >is = 7 and a3 which is the train|hiring|other hrs column to add only the
    >> >1train is = 1.
    >> >i have 6 different hrs columns with 3 or more different depts.
    >> >the order of depts can vary in order and combination.
    >> >eg)
    >> >
    >> >a1 shift worked column a1= 3cash 4office 1other
    >> >a1=3stock
    >> >2train 2pay
    >> >a2 cash|office|pay column a2= 7
    >> >a2=2
    >> >a3 stock|train|rec column a3= 0
    >> >a3=5
    >> >a4 other|front|ret|rel column a4=1
    >> >a4=0

    >>
    >> If this is all you've wanted, we've wasted a lot of time.
    >>
    >> In your first post, you indicated that the numbers would be 1-8.
    >>
    >> And you've always had the strings formatted with no space between the
    >> number
    >> and the description.
    >>
    >> All you need to do to pull out the number associated with a particular
    >> descriptor is:
    >>
    >> =MID(A1,FIND(descriptor,A1)-1,1) or, for the office:
    >>
    >> =MID(A1,FIND("office",A1)-1,1)
    >>
    >> And you can use the add operator to add these together.
    >>
    >> So if you wanted to add only the cash and office from a string
    >>
    >> "3cash 4office 1train"
    >>
    >> all you need to do is:
    >>
    >> =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
    >>
    >> You should be able to figure out the rest by yourself.
    >>
    >>
    >> --ron
    >>




  27. #27
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Tue, 7 Feb 2006 20:48:13 -0800, "cj" <[email protected]> wrote:

    >hi ron
    >
    >thanks for your answers and patience, i really appreciate your help.
    >i play with the formulas
    >
    >a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1,"stock|train|rec|st|tr")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    >
    >a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1,"cash|office|pay|cs|off|py")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    >
    >these 2 formulas work great: when i enter in a1 just "cash" a2 returns with
    >8 and same for cash. thats what i want and i can add extra words if the shift
    >is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine
    >except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock",
    >both a2 and a3 return with 8. its there any way can separate them. i need a2
    >to return with 2 and a3 to return with 5?


    Reread my last post and use the formulas there. For what you've finally
    described, you don't need morefunc or the REGEX functions at all.

    ===========================
    All you need to do to pull out the number associated with a particular
    descriptor is:

    =MID(A1,FIND(descriptor,A1)-1,1) or, for the office:

    =MID(A1,FIND("office",A1)-1,1)

    And you can use the add operator to add these together.

    So if you wanted to add only the cash and office from a string

    "3cash 4office 1train"

    all you need to do is:

    =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)

    You should be able to figure out the rest by yourself.
    ==============================
    --ron

  28. #28
    cj
    Guest

    Re: extracting numbers

    ron

    this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
    works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but
    returns with #value! error when i enter just 8cash or left blank for that day
    off. with the regex formula i can enter "-" for the blank day and my totals
    on the bottom still works and i can change and add combination of the words
    cash|cs|office|of and i can add extra words after them "3cash training
    2office(front).

    "Ron Rosenfeld" wrote:

    > On Tue, 7 Feb 2006 20:48:13 -0800, "cj" <[email protected]> wrote:
    >
    > >hi ron
    > >
    > >thanks for your answers and patience, i really appreciate your help.
    > >i play with the formulas
    > >
    > >a2=IF(REGEX.FIND("stock",a1)=1,8,IF(REGEX.FIND(a1,"stock|train|rec|st|tr")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    > >
    > >a3=IF(REGEX.FIND("cash",a1)=1,8,IF(REGEX.FIND(a1,"cash|office|pay|cs|off|py")>0,EVAL(REGEX.SUBSTITUTE(a1&"0","(\D+)","+")),""))
    > >
    > >these 2 formulas work great: when i enter in a1 just "cash" a2 returns with
    > >8 and same for cash. thats what i want and i can add extra words if the shift
    > >is not 8hrs eg: 3cash 2office 2pay. up to this point everything is fine
    > >except when a1 has both a2 and a3 depts eg: a1 is " 3cash 2office 2stock",
    > >both a2 and a3 return with 8. its there any way can separate them. i need a2
    > >to return with 2 and a3 to return with 5?

    >
    > Reread my last post and use the formulas there. For what you've finally
    > described, you don't need morefunc or the REGEX functions at all.
    >
    > ===========================
    > All you need to do to pull out the number associated with a particular
    > descriptor is:
    >
    > =MID(A1,FIND(descriptor,A1)-1,1) or, for the office:
    >
    > =MID(A1,FIND("office",A1)-1,1)
    >
    > And you can use the add operator to add these together.
    >
    > So if you wanted to add only the cash and office from a string
    >
    > "3cash 4office 1train"
    >
    > all you need to do is:
    >
    > =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)


    >
    > You should be able to figure out the rest by yourself.
    > ==============================
    > --ron
    >


  29. #29
    Ron Rosenfeld
    Guest

    Re: extracting numbers

    On Wed, 8 Feb 2006 11:26:40 -0800, "cj" <[email protected]> wrote:

    >ron
    >
    >this formula =MID(A1,FIND("cash",A1)-1,1)+MID(A1,FIND("office",A1)-1,1)
    >works fine if i enter in a1 : 3cash 2office 1train or 3cash 2office,but
    >returns with #value! error when i enter just 8cash or left blank for that day
    >off. with the regex formula i can enter "-" for the blank day and my totals
    >on the bottom still works and i can change and add combination of the words
    >cash|cs|office|of and i can add extra words after them "3cash training
    >2office(front).


    I see what you mean. And I'm glad you've got something working for yourself.

    To return a zero when the descriptor does not exist, you could also use a
    formula like:

    =REGEX.MID(A1&"0","\d(?=descriptor|$)")

    or, for cash:

    =REGEX.MID(A1&"0","\d(?=cash|$)")

    or, to allow for the possibility of a <space> between the number and the
    descriptor:

    =REGEX.MID(A1&"0","\d(?=\s*(descriptor|$))")





    --ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1