How Do I Extract the First Five Words of an Excel Cell?

2 Comments

1. aladou says:

   February 9, 2010 at 7:05 pm

   So from each cell, you want to pull out the first five words?

   The first and simplest thing to try is text to columns:
   - Data – Text to Columns
   - step 1) pick Delimited
   - step 2) pick what the words are separated by (comma, space)
   - step 3) pick all of the columns after the first 5, and pick Do not import (skip)
   - and Finish
   This will leave the first word in the original column, and put the other words in one column after another to the right.

   If the above doesn’t meet your needs, then we would need formulas to find where the separators are (commas, spaces, etc.), and then find the words between the separators. And that’s do-able, but we would need to know what separates the words (commas or just spaces), and whether you want the five words all in one cell (on each row), or in 5 separate cells.
   ________
   EDIT:
   I’ll just go ahead and assume you need the formulas, and that the separators are just spaces, but that’s easy enough to change:
   First, find the locations of the spaces (first one in H1, second in I1, etc.). Assumes the cell to be searched is in A1:
   in H1: =IF(ISERROR(SEARCH(” “,$A1)), 0, SEARCH(” “,$A1))
   in I1: =IF(H1>0,IF(ISERROR(SEARCH(” “,$A1,H1+1)), 0, SEARCH(” “,$A1,H1+1)),0)
   in J1: =IF(I1>0,IF(ISERROR(SEARCH(” “,$A1,I1+1)), 0, SEARCH(” “,$A1,I1+1)),0)
   in K1: =IF(J1>0,IF(ISERROR(SEARCH(” “,$A1,J1+1)), 0, SEARCH(” “,$A1,J1+1)),0)
   in L1: =IF(K1>0,IF(ISERROR(SEARCH(” “,$A1,K1+1)), 0, SEARCH(” “,$A1,K1+1)),0)
   in M1: =IF(L1>0,IF(ISERROR(SEARCH(” “,$A1,L1+1)), 0, SEARCH(” “,$A1,L1+1)),0)

   If the separators are commas instead of just spaces, then you would replace the ” ” in these formulas with “,”.

   Now that you know where the separators are, you look for the words.
   - If they can all be in the same cell, then it’s simple:
   =LEFT(A1,L1-1)

   - If they have to be in different cells, then let’s say we put them in C1 through G1:
   in C1: =IF(H1=0,$A1, LEFT($A1,H1-1))
   in D1: =IF(H1=0,”", IF(I1=0,MID($A1, H1+1,LEN($A1)-H1), MID($A1,H1+1,I1-H1-1)))
   in E1: =IF(I1=0,”", IF(J1=0, MID($A1,I1+1, LEN($A1)-I1), MID($A1,I1+1,J1-I1-1)))
   in F1: =IF(J1=0,”", IF(K1=0, MID($A1,J1+1, LEN($A1)-J1), MID($A1,J1+1,K1-J1-1)))
   in G1: =IF(K1=0,”", IF(L1=0, MID($A1,K1+1, LEN($A1)-K1), MID($A1,K1+1,L1-K1-1)))

   That’s all (sic).
   Any questions, let me know.
   .
2. Chaminda says:

   February 9, 2010 at 7:13 pm

   I hope there is space in between words. Here is the formula

   =IF(ISERROR(LEFT(A2,SEARCH(1,SUBSTITUTE(A2,” “,1,5))-1))=TRUE,A2,LEFT(A2,SEARCH(1,SUBSTITUTE(A2,” “,1,5))))

   A2=set of words
   ” “=Space

   Below is the breakup of the formula.

   =IF(ISERROR(LEFT
   (A2,SEARCH(1,SUBSTITUTE
   (A2,” “,1,5))-1))=TRUE,A2,LEFT
   (A2,SEARCH(1,SUBSTITUTE(A2,” “,1,5))))

   Try this formula with the A2 entry.

   (You can see the whole formula if you move the mouse over it)

   If your words are separated from something other than space, let me know. We can do that also.

   Another option is to use “text to column”. I think this one has already been explained by the other answerer.