# Split words in column and add results

1. ## 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. ## 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!

3. ## 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):

4. ## 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. ## Re: Split words in column and add results

that worked perfectly!! thanks so much!!

6. ## Re: Split words in column and add results

You're welcome. Thanks for the feedback!

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

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

#### 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