Back to Excel Newsletter Archives

**Software Categories **Search Software

Excel **Add-ins** || Excel **Training ** || Excel **Templates** || **Employee Scheduling Software** || Excel** Password Recovery** and Access & Word etc|| Excel Recovery and Access & Word etc || **Financial** Software || Financial **Calculators** || **Conversion** Software || **Construction** **Estimating** Software || **Drilling Software** || **Real Estate** Investment Software || Time** Management** Software || **Database** Software || **Neural** Network Software || **Trading** Software || **Charting** Software || **Windows** & **Internet** Software || **Barcodes ** Fonts, ActiveX, Labels and DLL's || **Code Printing** Software

COMPLETE EXCEL COURSE HALF PRICE SPECIAL!

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

**Extracting Words From a String of Words**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.

Text Sting in

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

Note the use of

Text Sting in

"Our main business focus is Excel Spreadsheets"

Formula/Function is;

=LEFT(A4,FIND(" ",A4)-1)

Formula/Function result is;

Our

Text Sting in

"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 a custom Excel VBA function that makes this a lot simpler, see the Excel VBA Tips & Tricks below.

Unlike Microsoft Word, Excel does not give us a ready made way to find out the number of words in a cell, or a range of cells containing text, or words. However, with the help of the SUBSTITUTE function/formula and the LEN function/formula we can. If you are not familiar with these functions/formulas I have written an explanation below.

=substitute(text,old_text,new_text,instance_num)

Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

=SUBSTITUTE(A1, "Sales", "Cost") If A1 had the text "Sales Data" the formula result would be "Cost Data".

=len(text)

LEN returns the number of characters in a text string.

=LEN(A1) If

The formula below will return the number of words (not characters) in cell

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Be aware that superfluous spaces are also counted and may give misleading results. To ensure accuracy we can simply nest the TRIM formula function/formula in the first LEN

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

The formula below will return the number of words (not characters) in cells

=LEN(TRIM(A1&A2&A3&A4&A5))-LEN(SUBSTITUTE(A1&A2&A3&A4&A5," ",""))+1

With the aid of Excel VBA we can write a custom formula/function, or user defined function to extract out the nth word from a text string. The code below should be placed in a standard Excel Module after entering the VBE. That is, push **Alt**+**F11** and then go to **Insert**>**Module** and paste in the code below;

Option Compare TextFunction Get_Word(text_string As String, nth_word) As StringDim lWordCount As Long With Application.WorksheetFunction lWordCount = Len(text_string) - Len(.Substitute(text_string, " ", "")) + 1 If IsNumeric(nth_word) Then nth_word = nth_word - 1 Get_Word = Mid(Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _ .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256), 2, _ .Find(" ", Mid(Mid(.Substitute(text_string, " ", "^", nth_word), 1, 256), _ .Find("^", .Substitute(text_string, " ", "^", nth_word)), 256)) - 2) ElseIf nth_word = "First" Then Get_Word = Left(text_string, .Find(" ", text_string) - 1) ElseIf nth_word = "Last" Then Get_Word = Mid(.Substitute(text_string, " ", "^", Len(text_string) - _ Len(.Substitute(text_string, " ", ""))), .Find("^", .Substitute(text_string, " ", "^", _ Len(text_string) - Len(.Substitute(text_string, " ", "")))) + 1, 256) End If End WithEnd Function

Now come back to Excel proper and use in any cell like shown below. For all examples the full text string is in cell **A1** and the text string is:

"Our main business focus is Excel Spreadsheets"

=Get_Word(A1,"Last")

would result in the word "Spreadsheets" as that word is the last word.

=Get_Word(A1,"First")

would result in the word "Our" as that word is the 1st word.

=Get_Word(A1,6)

would result in the word "Excel" as that word is the 6th word.

Find the Nth Occurrence of Specified Value

The custom function/formula below was written in Excel 2003 and may not work in earlier Excel versions.

Function Nth_Occurrence(range_look As Range, find_it As String, _ occurrence As Long, offset_row As Long, offset_col As Long)Dim lCount As LongDim rFound As Range Set rFound = range_look.Cells(1, 1) For lCount = 1 To occurrence Set rFound = range_look.Find(find_it, rFound, xlValues, xlWhole) Next lCount Nth_Occurrence = rFound.offset(offset_row, offset_col)End Function

The custom function/formula can now be used like shown below

=Nth_Occurrence($B$1:$B$22,"Harry",3,0,1)

The syntax is

=Nth_Occurrence(range_look,find_it,occurrence,offset_row,offset_col)

Where $B$1:$B$22 (range_look) is the range to find the 3rd occurrence (occurrence) of "Harry" (find_it). When found, it will return the value by offsetting 0 rows (offset_row) and 1 column (offset_col) to the right. The offset_row and offset_col arguments can be negative values if that is what is needed.

Until next month, keep Excelling!**ADVERTISEMENTS**Artificial neural network software for stock markets! EXCEL TEMPLATES SPECIALS DATABASE SOFTWARE