Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel: Extracting Words From Text in Excel

| | Information Helpful? Why Not Donate.

Parse Words From a Text String in Excel

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

Extracting Words From Text in ExcelSee Also: Extract nth Word From Text in Excel Custom Function/Formula || Count Words in Excel || Excel Formulas for Text, Dates & Numbers

Excel has very powerful and useful Text formula/functions that we can use to extract words from a string of words. Or, put another way, parse out specific words from text. The example below show how we can use the Text formula/functions to do this.

Get Last Word From Text String. See Also: Extract Last Word VBA

Text Sting in A2 is;
Our main business focus is Excel Spreadsheets

Formula/Function is;
=MID(SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),FIND("^",SUBSTITUTE(A2," ","^",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))+1,256)Formula/Function result is;
Spreadsheets

Get First Word From Text String

Text Sting in A4 is;
Our main business focus is Excel Spreadsheets

Formula/Function is;
=LEFT(A4,FIND(" ",A4)-1)

Formula/Function result is;
Our

Get Nth Word From Text String (4th word in this case)

Text Sting in A6 is;
Our main business focus is Excel Spreadsheets

Formula/Function is;
=MID(MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A6," ","^",3),1,256),FIND("^",SUBSTITUTE(A6," ","^",3)),256))-2)

Formula/Function result is;
focus

This last one can seem a bit over-whelming. It is the 4 occurrences of the number 3 that determines that we parse out the 4th word. In other words, to get the 5th word all occurrences of the number 3 would need to be changed to number 4. If it was the 2nd word we wanted, we would change all occurrences of the number 3 to number 1. Please note, the above formula cannot be used to get the first or last word. For this you can use: Extract nth Word From Text in Excel

Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money

Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft