+ Reply to Thread
Results 1 to 2 of 2

Cell having multiple lines of text associated with numbers - where I need the total SUM

  1. #1
    Registered User
    Join Date
    03-28-2015
    Location
    Sri Lanka
    MS-Off Ver
    10
    Posts
    1

    Cell having multiple lines of text associated with numbers - where I need the total SUM

    Problem:

    A1
    Book1 2500
    Book2 500
    Book3 1000

    B1 - I need the total SUM of the number values in A1 which is 4000

    Can someone get the formula for B1 please?

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

    Re: Cell having multiple lines of text associated with numbers - where I need the total SU

    =SUM(IFERROR(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," ")," ",REPT(" ",LEN(A1))),(ROW(1:20)-1)*LEN(A1)+1,LEN(A1)))+0,0))
    TRY THIS ARRAY FORMULA (Shift+Ctrl+Enter)
    IT WILL WORK FOR 20 Items
    if it is more than that edit ROW(1:20) as per your requirement
    Samba

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

+ 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] Split Wrapped Text (Multiple Lines) in A Cell to Multiple Columns
    By csmiin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-28-2012, 09:15 PM
  2. [SOLVED] multiple numbers , one cell, one total ex 1,3,7 = 11
    By kai888 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-24-2012, 10:25 AM
  3. Need a macro to total different lines and move total to new cell
    By cmefix in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-09-2012, 02:08 PM
  4. adding numbers for multiple text lines
    By nylarising in forum Excel General
    Replies: 6
    Last Post: 11-01-2007, 01:50 PM
  5. Multiple lines of text in a cell
    By tk in forum Excel General
    Replies: 1
    Last Post: 05-26-2006, 11:20 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