Newsletter Index

EXCEL TIPS AND TRICKS

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

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.

Count Words in a Cell or Range of CellUnlike 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 doesSubstitutes 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 doesLEN 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 CellThe 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;

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.