We help IT Professionals succeed at work.
Get Started

Excel - Parsing a text string, first words, last words

billb1057 asked
Last Modified: 2012-05-09
I have a file with text entries in each row of a column (call it E2:E500).  Each entry has about 50 words.  I would like to extract the first four words  of each string in column F and the last four words in column G.
I could do the first word with =MID(E2,1,FIND(" ",E2)-1)
So, that gets the string from the first character to the first space.
Now to get the second word, I guess you would start with the first space:
=MID(E2,FIND(" ",E2)+1,FIND(" ",E2))
Now I'm not sure on the third word or the fourth word.  And then at the end, do you concatenate all the strings together?  Then the problem is multiplied by finding the last four words.
Or is there an easier way to count the spaces and then get all the words in a string fragment?
Thanks for whatever you can do with this.
I can upload a file, if needed, but basically any cells with text strings (more than 10 words each, for example) would be fine.
Watch Question
This problem has been solved!
Unlock 4 Answers and 46 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE