+ Reply to Thread
Results 1 to 24 of 24

How do I extract a number from a cell and use it in a formula in another cell?

  1. #1
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    How do I extract a number from a cell and use it in a formula in another cell?

    I’m working on a project and I’m having some trouble figuring out a formula. I’ll try to keep this short and sweet.

    I want to type:

    “Kansas City, MO (83/46)” into A1

    “.75” into B1

    “2” into C1

    “5” into D1

    I then want to put a formula in E1 that will multiply the “46” from A1 by B1 “.75” and then multiply that number by C1 “2” and then add D1 “5” times the “46” from A1 again. In my head it looks like this:

    =46*B1*C1+(D1*46) This will put 299 in E1

    I just don’t know how to extract the 46 from A1.

    For those that are curious…Kansas City, MO (83/46) represents a location that one of our employees is traveling to; 83 is the lodging rate and 46 is the per diem rate. When someone travels they receive .75% per diem for their first and last travel days (represented in B1 by the number 2), and 100% per diem for every full day they are on travel (represented in C1 by the number 5). So this traveler will receive $299 in per diem for traveling to Kansas City, MO leaving on a Sunday and returning the following Saturday.

    =$46*.75*2+(5*46)

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    =mid(a1,find("/",a1)+1,2)*b1*c1+(d1*mid(a1,find("/",a1)+1,2))
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    You might have to put the 83 and 46 in separate columns and shift everything over 2 columns. So, A would be City, St, B would be lodging, C would be Per Diem. Then you could use, in G1
    Please Login or Register  to view this content.
    Edit* sorry, didn't see the other post......Or just go with daffodil's suggestion
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  4. #4
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    That 100% worked! I'm kicking myself for not signing up sooner! Thanks a million for the help!

    From this day forward my life will never be the same!

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

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    You might have to put the 83 and 46 in separate columns and shift everything over 2 columns.

    I suggest you use the solution in #3.
    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.

  6. #6
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    =trim(right(substitute(substitute(a1,")",""),"/",rept(" ",256)),256))*(b1*c1+d1)
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  7. #7
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Or this........

    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  8. #8
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.

  9. #9
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    This worked

    =mid(a1,find("/",a1)+1,2)*b1*c1+(d1*mid(a1,find("/",a1)+1,2))

    But I need to start the formula on F1 instead of A1 as I originally thought. How do I update it? What does the “1,2” represent. I know that I’ll need to change A1 to F1 etc.

  10. #10
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Disregard. I figured it out. I wasn't taking the header into account and was starting on F3 instead.

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Yeah, definitely post in the forum more.

    If you can't figure it out in 5 minutes, and you can't teach yourself the answer in 10, put it up here.

  12. #12
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    I agree with daffodil 100% on the above comment.

  13. #13
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    I thought I could decipher from the previous answer, but I haven’t figured it out yet. Now I want to multiply the “83” by a number in “J3”

    “Kansas City, MO (83/46)” into F3

    “7” into J3

    I want the answer (581) to show up in “AA3”

  14. #14
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,904

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    =trim(mid(substitute(substitute(f3,"(",rept(" ",256)),"/",rept(" ",256)),256,256))*(j3)

  15. #15
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    MID which is short for MIDDLE pulls from a reference, starting at the nth character, and going for x characters.

    Because we can't be sure that the number preceding the "/" will be double digit, I'd use nflsales' solution which inserts lots of spaces around the characters so it always grabs the correct value.
    Last edited by daffodil11; 06-04-2015 at 03:20 PM.

  16. #16
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    See all these convoluted formulas people are offering?

    The first rule of spreadsheet design is: Keep It Simple Stupid.

    If you put those numbers in separate cells your life will be so much easier and you'll be in compliance with rule 1 of spreadsheet design!

    Data Range
    A
    B
    C
    1
    Location
    Lodging Rate
    Per Diem Rate
    2
    Kansas City, MO
    83
    46
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  17. #17
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    That's 2 people now that have sided with me on my simple solution....YAY Me! I got something right for a change.

  18. #18
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    nflsales' solution works well. The numbers will always be at least 2 digits if that matters.

  19. #19
    Registered User
    Join Date
    06-04-2015
    Location
    Columbia, MO
    MS-Off Ver
    2010
    Posts
    19

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    I agree that simple is always nice, but I know simple, and I'm hoping that I can expand my horizons by trying some of the more complicated things. Plus...complicated around here = job security!

  20. #20
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    There are tons of formula applications in Excel that are complicated by their nature but this shouldn't be one of them!

    It's great that you're interested in learning more advanced concepts but one aspect of being an "advanced user" is knowing how to make things simple!

  21. #21
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Tony just called all of us stupid.

  22. #22
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Time to move on...

  23. #23
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Well, not all of us

  24. #24
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,647

    Re: How do I extract a number from a cell and use it in a formula in another cell?

    Quote Originally Posted by daffodil11 View Post
    Tony just called all of us stupid.
    lol...
    You shouldn't mind all that. Probably he needs to learn how to express his views on a public forum otherwise I also agreed with the point he raised.

+ 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] Formula to extract Registration Number (ABN) from text in a cell
    By &Roo in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-20-2015, 05:39 AM
  2. [SOLVED] Need to find a formula to extract a number out of a cell that also has letters
    By Cassi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-25-2013, 09:39 PM
  3. Excel Formula to Extract a Number from a Cell
    By housinganalyst in forum Excel General
    Replies: 4
    Last Post: 07-29-2011, 11:41 AM
  4. extract number to a different cell
    By aranechan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-07-2011, 12:54 PM
  5. [SOLVED] extract number and use in formula from text & numbers in cell
    By ivory_kitten in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-14-2006, 12:45 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