» Extracting the First N Number of Words from a String
CATEGORY - Excel Text Formulas
VERSION - All Microsoft Excel Versions
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:
Recommended Books:
- Preparing the Marketing Plan (Ama Marketing Toolbox Series New Edition)
- Marketing Plans That Work, Targeting Growth and Profitability
- Marketing Plans
- Analyzing Markets, Products, and Marketing Plans
- MP Managerial Accounting w/ Topic Tackler, Net Tutor, & PowerWeb
- Your First Business Plan: A Simple Question and Answer Format Designed to Help You Write Your Own Plan (3rd Ed)
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: