+ Reply to Thread
Results 1 to 18 of 18

sum a value(substring) in a cell

  1. #1
    Registered User
    Join Date
    05-15-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    sum a value(substring) in a cell

    Column A

    26||12may09||some
    4||30june10||text
    122||6Jan02||here
    54||22may04||whatever

    Hi,
    Would it be possible to sum the first part (the number on the left of the separator ||) of a string in a cell ?

    tnchong

  2. #2
    Registered User
    Join Date
    05-15-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: sum a value(substring) in a cell

    Sorry, I mean

    Would it be possible to sum the first part (the number on the left of the separator ||) of a string in a column of cell ?

  3. #3
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    1,938

    Re: sum a value(substring) in a cell

    Try this

    =SUM(--LEFT(A1:A4,FIND("|",A1:A4)-1))

    Commit with Ctrl+Shift+Enter

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: sum a value(substring) in a cell

    It would be far better if the data were already separated into columns.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    05-15-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: sum a value(substring) in a cell

    Quote Originally Posted by windknife View Post
    Try this

    =SUM(--LEFT(A1:A4,FIND("|",A1:A4)-1))

    Commit with Ctrl+Shift+Enter

    Thanx pal, it works though I cannot understand the -- sign and difference between just hit enter and Ctrl+Shift+Enter

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum a value(substring) in a cell

    Regards --
    This is known as the double unary operator and is used to coerce a number stored as text to a number, ie --"10" becomes 10

    Regards CTRL + SHIFT + ENTER
    This is an Array formula and is often referred to as a CSE formula ... see the link in my sig to Colin_L's Array tutorial/discussion for a more in depth review on the subject.

    You could possibly revert to a SUMPRODUCT (normal ENTER entry) over a SUM CSE, eg the below mimics the previously detailed CSE:

    =SUMPRODUCT(--(LEFT(A1:A4,FIND("|",A1:A4)-1)))

    however both the CSE & the above SUMPRODUCT are in their present form are however open to error if any cell within A1:A4 fails to contain the pipe symbol and/or entry contains alpha text only.
    In this regard the previously detailed SUMPRODUCT could be strengthened slightly to:

    =SUMPRODUCT(--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))

    However should the range contain non-numerics pre pipe then it would still generate an error.
    Last edited by DonkeyOte; 05-16-2009 at 02:56 AM.

  7. #7
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: sum a value(substring) in a cell

    Or you can select your data and DATA->TEXT TO COLUMNS-> delimited -> in other write |

    And you'll get separated data for numbers, dates and data

  8. #8
    Registered User
    Join Date
    05-15-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: sum a value(substring) in a cell [amended]

    Column A

    26||12may09||brad
    4||30june10||
    37||6Jan02||here
    54||22may04||whatever
    2||16Jan05||here
    54||12may08||pitt
    26||2may99||
    4||7june01||text

    Hi,
    I would like to make a little bit of amendment here. How can I conditionally sum the numbers on the left of the sepator when seeing the word "here" on the right side of the sepator. i.e. only 37 and 2 should be added.

    Any bright ideas eh ?

  9. #9
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: sum a value(substring) in a cell

    This formula will inore the text and sum only number on left side of pipe symbol
    Don't forget to press CSE (Ctril + Shift + Enter)

    {=SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0))}
    If this post helps, Please don't 4get to click the star icon located at the bottom left of my Post.

  10. #10
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum a value(substring) in a cell

    I would opt for:

    =SUMPRODUCT(--(RIGHT(A1:A10,5)="|here"),--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))

  11. #11
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: sum a value(substring) in a cell

    Quote Originally Posted by DonkeyOte View Post
    I would opt for:

    =SUMPRODUCT(--(RIGHT(A1:A10,5)="|here"),--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))
    I came to smiliar...

    =SUMPRODUCT(--(RIGHT(A1:A8;4)="here");1*(LEFT(A1:A8;FIND("|";A1:A8;1)-1)))

    of course, better is to look for |here but I was wondering what 0"&A1:A10 doing?

  12. #12
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: sum a value(substring) in a cell

    The 2nd result can be achieved with this .... But i think Sumproduct is good approach if you have some knowledge about it .......

    Don't forget to press CSE (Ctril + Shift + Enter)
    =SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here")))

  13. #13
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: sum a value(substring) in a cell

    Quote Originally Posted by zbor View Post
    I came to smiliar...

    =SUMPRODUCT(--(RIGHT(A1:A8;4)="here");1*(LEFT(A1:A8;FIND("|";A1:A8;1)-1)))

    of course, better is to look for |here but I was wondering what 0"&A1:A10 doing?
    I think this is to control error that if you have nothing on left side of "|" then your formula will give error .. what you think DK ????
    Last edited by mubashir aziz; 05-18-2009 at 03:39 AM.

  14. #14
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum a value(substring) in a cell

    To handle blanks...ie blanks become "0|"
    (so you can apply the formula to a range containing blanks be they at the end or interspersed amongst other entries)

    If you read the earlier posts all methods would still fail if a given cell in the range was non-blank but failed to contain a number in the left most section (ie "aaa") ... this could be accounted for but is most likely not required.
    Last edited by DonkeyOte; 05-18-2009 at 03:40 AM.

  15. #15
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: sum a value(substring) in a cell

    Quote Originally Posted by DonkeyOte View Post
    If you read the earlier posts all methods would still fail if a given cell in the range was non-blank but failed to contain a number in the left most section (ie "aaa") ... .
    DK but my formula will always work even there will be no "|" or blank cell .....

  16. #16
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: sum a value(substring) in a cell

    Yes it would, apologies mubashir - I should have reviewed more thoroughly before casting dispersions

  17. #17
    Forum Contributor mubashir aziz's Avatar
    Join Date
    03-18-2009
    Location
    Lahore, Pakistan
    MS-Off Ver
    MS Office 2013
    Posts
    533

    Re: sum a value(substring) in a cell

    A more powerfull approach if there is no match found ...

    Don't forget to press (CSE)
    =IF(SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here")))=0,"No Match Found",SUM(IF(ISNUMBER(--LEFT(A1:A4,FIND("|",A1:A4)-1)),(--LEFT(A1:A4,FIND("|",A1:A4)-1)),0)*(--(RIGHT(A1:A4,5)="|here"))))
    @ DK No problem as you are one of those persons from forum bcoz of them I'm learning a lot …..

  18. #18
    Registered User
    Join Date
    05-15-2009
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: sum a value(substring) in a cell

    Quote Originally Posted by DonkeyOte View Post
    I would opt for:

    =SUMPRODUCT(--(RIGHT(A1:A10,5)="|here"),--(LEFT("0"&A1:A10,FIND("|","0"&A1:A10&"|")-1)))
    Thanx 4 your prompt reply, this one works.
    Kindly if you would tell me what the "0" and "&s" are for ?
    You know, when I try to modify the parameters in the FIND dialogue
    the Within_text category shows "0"&D3:AS3&"|" #value!

+ 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