+ Reply to Thread
Results 1 to 12 of 12

Extract Number from Parenthesis for Use in Formula

  1. #1
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Extract Number from Parenthesis for Use in Formula

    I have the following text in a cell:

    DURA-SKRIM R12BV (38 lbs/msf)

    I need to extract the number 38 from the cell and use as the divisor of 10,000 then multiply the result by 1000 round that result to the nearest 5K and populate in a separate cell. So for example the output I would be looking for in this example would be 265,000. I will need to replicate this formula in other cells that will have the same format but differing numbers of characters for example:

    DuraSkrim J25DT1 Textured 1-Side (115 lbs/msf)

    In this case I would want to extract the number 115 from the cell and perform the same operation

    Thanks in advance for your assistance.
    Last edited by kellyjo7; 01-09-2015 at 05:35 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Extract Number from Parenthesis for Use in Formula

    Try this assuming there will not be any other occurances of ( in the string..

    =(MID(A1,FIND("(",A1)+1,FIND(" ",A1,FIND("(",A1))-FIND("(",A1)-1))+0

  3. #3
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    Awesome! That appears to work, is there anyway I can get it to round the result to the nearest 5K? When I plug this into the formula. Right now I have
    =(10000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000

    Which gives me 263,158, Which I would want to round to 265,000.

    Now say if the result was 262,999 or below I would want to round to 260,000.

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract Number from Parenthesis for Use in Formula

    See the formula in the green cell for the result, in the attached file.

    Please add the translated formula on the forum.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Extract Number from Parenthesis for Use in Formula

    Here's a way to round a number to the nearest 5000 (could be applied to the nearest anything)

    =INT(A1/5000)*5000

    It basically devides the value by 5000, then chops off the Decimal value, then multiplies back by 5000

  6. #6
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    Great thanks, this is the final formula I used:

    =MROUND((10000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000)

    DURA-SKRIM R12BV (38 lbs/msf)

    was in Cell B25

    One final question is there any way to combine the output of two different formulas in the same cell and separate with the text "to" or a dash?

    There is a chance I wanted to also calculate with a different (larger) dividend then 10,000 and include in the same cell as a range by inserting the text "to" or a dash would work as well. For example say I used 20,000 as the dividend

    I would want the output to be:

    265,000 to 525,000

    or as formulas using the DURA-SKRIM R12BV (38 lbs/msf) in cell B25 as an example:

    =MROUND((10000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000)

    to

    =MROUND((20000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000)
    Last edited by kellyjo7; 01-09-2015 at 06:14 PM.

  7. #7
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    I seemed to get it to work using this formula:

    =MROUND((10000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000) & " to " & MROUND((20000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))

    However, it takes away the commas. Anyway to do it and keep the comma's on the numbers?

  8. #8
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    Okay got it using this:

    =FIXED(MROUND((10000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000),0) & " to " & FIXED(MROUND((20000/(MID(B25,FIND("(",B25)+1,FIND(" ",B25,FIND("(",B25))-FIND("(",B25)-1))+0)*1000,5000),0)


    Thanks All!

  9. #9
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    Sorry, one last question.

    In the cell below the one that will be out put with:

    265,000 to 525,000

    I want it to automatically take one less than the smaller result (in this case 265,000) and combine with & Below

    So it will out put 264,999 & Below

    And in the cell above 265,000 to 525,000, I will want it to output 525,001 & up

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract Number from Parenthesis for Use in Formula

    =celreference-1 &" Below"

    =celreference+1 &" Up"

  11. #11
    Registered User
    Join Date
    12-08-2014
    Location
    South Dakota
    MS-Off Ver
    MS Office 365
    Posts
    50

    Re: Extract Number from Parenthesis for Use in Formula

    That doesn't work because there are two numbers in the referenced cell a low end 265,000 and upper end 525,000

  12. #12
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Extract Number from Parenthesis for Use in Formula

    Then use 2 cellreferences (that is the way I should go).

    untested:
    Other option is multiply your (2) formula *1 and abstract 1

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Extract all occurence between parenthesis
    By mwalol in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-03-2014, 07:58 AM
  2. Replies: 9
    Last Post: 05-28-2014, 04:14 PM
  3. Series in parenthesis of formula
    By andersb in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-12-2011, 04:41 AM
  4. How do I add parenthesis to my phone number list -the area code?
    By Alan in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-10-2006, 01:00 PM
  5. [SOLVED] How do I format a negative number in parenthesis?
    By John Nollett in forum Excel General
    Replies: 10
    Last Post: 01-07-2006, 11:40 AM

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