+ Reply to Thread
Results 1 to 10 of 10

I need to extract and add the numbers

  1. 04-12-2023, 12:23 PM #1
    anonymous321
    anonymous321 is offline
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    130

    I need to extract and add the numbers

    Dear Forum Members,
    I hope everybody is having a great time. I have data present in column G. The sample of data from G2:5 is as below

    1 for rent 15 for sale
    16 for sale
    8 for sale
    4 for rent 1 for sale 1 Commercial

    I want an excel formula which can extract values for rent, for sale and for commercial and add them.

    Thanks in advance.
    Reply With QuoteRegister To Reply
  2. 04-12-2023, 12:25 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I need to extract and add the numbers

    Your description is very ambiguous. I do not know what the expected answers are. Please upload a sample sheet WITH expected answers.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  3. 04-12-2023, 12:26 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I need to extract and add the numbers

    Hi. Also...

    Is your forum profile showing the Excel PRODUCT that you need this request to work with?

    The best solutions often rely on knowing WHICH Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date. If you aren't sure, in Excel go to File/Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

    The three most recent Excel PRODUCTS are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the Version number in your profile (e.g. MS365 (PC) Version 2211). The version number is in the About Excel section further down the Account page.

    Cheers,

    Glenn.
    Reply With QuoteRegister To Reply
  4. 04-12-2023, 12:28 PM #4
    anonymous321
    anonymous321 is offline
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    130

    Re: I need to extract and add the numbers

    Values from G2:G5 are

    1 for rent 15 for sale
    16 for sale
    8 for sale
    4 for rent 1 for sale 1 Commercial

    There respective answers after addition are

    16
    16
    8
    6
    Reply With QuoteRegister To Reply
  5. 04-12-2023, 12:31 PM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I need to extract and add the numbers

    OK. That's clear now... What about your Excel Product/version?? (See Post 3)
    Reply With QuoteRegister To Reply
  6. 04-12-2023, 12:35 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: I need to extract and add the numbers

    IF you are now using a recent version of O365, you can try:

    =SUM(IFERROR(TEXTSPLIT(A2," ")+0,0)

    If not, I'll not make any more guesses. I need to know what Excel Product you're using.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 04-12-2023, 01:34 PM #7
    Haluk
    Haluk is offline
    Valued Forum Contributor
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (24 H2) 64 Bit
    Posts
    1,187

    Re: I need to extract and add the numbers

    For older versions;

    (Data is starting from cell A1 to down...)

    Please Login or Register  to view this content.
    Last edited by Haluk; 04-12-2023 at 01:45 PM.
    Reply With QuoteRegister To Reply
  8. 04-12-2023, 01:53 PM #8
    anonymous321
    anonymous321 is offline
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    130

    Re: I need to extract and add the numbers

    Quote Originally Posted by Haluk View Post
    For older versions;

    (Data is starting from cell A1 to down...)

    Please Login or Register  to view this content.
    This gives error "This formula uses more levels of nesting than you can use current file format."
    Reply With QuoteRegister To Reply
  9. 04-12-2023, 01:55 PM #9
    anonymous321
    anonymous321 is offline
    Forum Contributor
    Join Date
    07-19-2017
    Location
    usa
    MS-Off Ver
    2016 Professional Plus
    Posts
    130

    Re: I need to extract and add the numbers

    Quote Originally Posted by Glenn Kennedy View Post
    IF you are now using a recent version of O365, you can try:

    =SUM(IFERROR(TEXTSPLIT(A2," ")+0,0)

    If not, I'll not make any more guesses. I need to know what Excel Product you're using.
    The office I have is "Microsoft Office Professional Plus 2016".
    Reply With QuoteRegister To Reply
  10. 04-12-2023, 02:01 PM #10
    Haluk
    Haluk is offline
    Valued Forum Contributor
    Join Date
    02-14-2019
    Location
    Turkiye
    MS-Off Ver
    2010 - 64 Bit on Windows-11 (24 H2) 64 Bit
    Posts
    1,187

    Re: I need to extract and add the numbers

    Quote Originally Posted by anonymous321 View Post
    This gives error "This formula uses more levels of nesting than you can use current file format."
    My formula works in Office 2010 and should work in your 2016 also....

    Can you try the attached workbook?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extract numbers between symbols, calculate and join numbers
    By nagesh.tvsr in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 03-11-2023, 06:57 AM
  2. Extract all numbers from string, returning numbers to individual cells
    By LJBOS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2020, 08:46 AM
  3. [SOLVED] Extract the text and numbers basis on specific numbers included
    By Neilesh Kumar in forum Excel General
    Replies: 6
    Last Post: 08-26-2018, 01:34 PM
  4. how to extract unique numbers once from a list of repeated numbers?
    By stuart.kwok@gmail.com in forum Excel General
    Replies: 2
    Last Post: 05-02-2006, 11:20 AM
  5. [SOLVED] How do I extract numbers from a cell with both text and numbers?
    By SHANNON in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-01-2005, 10:35 PM
  6. extract numbers from cell containing text & numbers
    By transferxxx@gmail.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-14-2005, 03:10 AM
  7. [SOLVED] Extract Fractional Numbers as Numbers NOT Date
    By MrBill in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1