Results 1 to 7 of 7

Thread: SUM only numbers in a cell that also contains text.
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default SUM only numbers in a cell that also contains text.

    Hi, I'm sure this will be fairly straightforward for someone here...

    I have a group of cells that I want adding together, they are cells G65, K65, O65 and S65.

    Each one of these cells contains both text and numbers and I only want to add the number part of the cell. They all contain the same formatting, they say the word Achieved, have one space and then the number value.

    I have looked at using SUMIF and ISNUMBER but all to no avail.

    I would be very grateful if someone can point me in the right direction!

    Thank you

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    As the cells are all going to be formatted the same, you can try something like the below:

    =SUM(RIGHT(G65,LEN(G65)-SEARCH(" ",G65)),RIGHT(K65,LEN(K65)-SEARCH(" ",K65)), RIGHT(O65,LEN(O65)-SEARCH(" ",O65)), RIGHT(S65,LEN(S65)-SEARCH(" ",S65)))
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  3. #3
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    Or:

    =SUM(SUBSTITUTE(G65, "Achieved", ""), SUBSTITUTE(K65, "Achieved", ""), SUBSTITUTE(O65, "Achieved", ""), SUBSTITUTE(S65, "Achieved", ""))
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    Thank you! Th efforts option definitely works. Will try the second suggestion too as that appears to be more straightforward to understand what is going on.

  5. #5
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    Happy it works for you.

    The first option is just looking to the right of the space of each cell since you have a space before each number and the second option is just replacing the "Achieved" with nothing which leaves just the number.

    Added the second option since i thought it looks a lot easier to change if needed.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    31
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    That's great, thank you again

  7. #7
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,742
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SUM only numbers in a cell that also contains text.

    How about

    =SUMPRODUCT(--MID(CHOOSE({1,2,3,4},G65,K65,O65,S65),10,100))

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •