+ Reply to Thread
Results 1 to 6 of 6

Split words in column and add results

  1. #1
    Registered User
    Join Date
    06-06-2014
    Posts
    4

    Question Split words in column and add results

    Hello,

    I'm trying to figure out how to search all cells for the word abc (case sensitive) and add the number that precedes it.

    I'm completely new to excel (coming from bash/perl) and have figured out left right word extraction, however i can't figure out how to do an if-then and sum

    Here's what i'm using for left/right extraction:
    =RIGHT(E2,FIND(" ",E2))
    =LEFT(E2,FIND(" ",E2)-1)

    Attached is a sample sample.xlsx

    Thanks!

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

    Re: Split words in column and add results

    This array formula** works on you sample data:

    =SUM(IF(EXACT(RIGHT(A2:F4,3),"abc"),--LEFT(A2:F4,2)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    But I have a feeling that your REAL data is more complex!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-06-2014
    Posts
    4

    Re: Split words in column and add results

    the only difference between the real data is that the left word can be from 1-3 digits and the right word can be from 3-7 characters long

    i tried changing the formula to this:
    =SUM(IF(EXACT(RIGHT(A2:V14,FIND(" ",A2:V14)),"abc"),--LEFT(A2:V14,FIND(" ",A2:V14)-1)))

    however, i get #VALUE!

    i was able to verify that it is being entered as an array formula (on mac it is command-enter) and i was able to verify your original formula worked on the sample data. i'm thinking it has something to do with the find

    Here is a closer sample (exact data i'm working with, just with a search and replace filter):
    Attached Files Attached Files

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

    Re: Split words in column and add results

    Maybe this...


    Data Range
    A
    B
    C
    D
    E
    F
    1
    group 1
    group 2
    2
    94 foo
    99 bar
    94 foo
    99 bar
    3
    69 foo
    88 bar
    55 ABC
    69 foo
    88 bar
    55 abc
    4
    94 bar
    88 xyz
    22 abc
    94 bar
    88 xyz
    22 abc
    5
    6
    7
    8
    9
    results:
    99

    Array entered**:

    =SUM(IF(ISNUMBER(FIND("abc",A2:F4)),--LEFT(A2:F4,FIND(" ",A2:F4))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  5. #5
    Registered User
    Join Date
    06-06-2014
    Posts
    4

    Re: Split words in column and add results

    that worked perfectly!! thanks so much!!

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

    Re: Split words in column and add results

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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. Macro to split words
    By Lana75 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-01-2009, 06:05 AM
  2. Split numbers and split words
    By Oddemann in forum Excel General
    Replies: 38
    Last Post: 05-10-2009, 02:57 PM
  3. Split 2 words
    By Roger in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-12-2006, 02:00 PM
  4. Split sentences into words
    By sparx in forum Excel General
    Replies: 5
    Last Post: 02-22-2006, 03:51 PM
  5. Split the words
    By Kelvin Lee in forum Excel General
    Replies: 1
    Last Post: 10-10-2005, 11:05 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