» 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:
- Special Edition Using Microsoft Excel 2002
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
- The McGraw-Hill 36-Hour Course in Finance for Nonfinancial Managers
- Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
- Quantitative Methods in Derivatives Pricing: An Introduction to Computational Finance
- Windows XP Pocket Reference
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: