OzGrid Excel and VBA Newsletter

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!

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL 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.

Get Last Word From Text String

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

Note the use of ^. This is used to replace the needed space character of the text string in A2. If your text includes ^ then choose another character that is NOT part of the text string.

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

Count Words in a Cell or Range of Cell
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

Syntax
=substitute(text,old_text,new_text,instance_num)

What it does
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.

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

LEN

Syntax
=len(text)

What it does
LEN returns the number of characters in a text string.

Example
=LEN(A1) If A1 had the text "Sales Data" the formula result would be 10 as A1 has 9 text characters and 1 space character.

Count Words in a Cell
The formula below will return the number of words (not characters) in cell A1

=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

Count Words in a Range of Cells

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

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

EXCEL VBA TIPS AND TRICKS

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 With    End 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!ADVERTISEMENTSArtificial neural network software for stock markets! EXCEL TEMPLATES SPECIALS DATABASE SOFTWARE