|  

» Extracting the First N Number of Words from a String

CATEGORY - Excel Text Formulas VERSION - All Microsoft Excel Versions
Problem:

Extracting the first three words from each of the strings in column A.

Solution:
Use the TRIM, LEFT, FIND, and SUBSTITUTE functions as shown in the following formula:
=TRIM(LEFT(A2,FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",3))-1))


Book Store:

VIEW ALL BOOKS

Recommended Books:

Print Tip
Add to My Tips
Rate This Tip
12 34 5
Rating: 3.83     Views: 17651
Bug fix
stephenb
:confused:
Good tip but it won't work properly if the text you work on has variable spacing between words. I think you might find the following works better:

[B]=LEFT(TRIM(A2),FIND("^",SUBSTITUTE(TRIM(A2)&" "," ","^",3))-1)[/B]

Cheers :rolleyes:
Click here to post comment
For Registered Users
Click here to Regiser
For Guest
Name
Comment Title
Comments


Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Copyright © 2003 ExcelTip.com Microsoft, Microsoft Excel is a U.S. registered trademark of Microsoft Corporation