This blog post describes how to create a list of unique words from a cell range. Unique words are all words that exist once in a given cell range.

Cell range B3:B15 contains values, see picture above.

1. Filter unique strings in a range - UDF

Rick Rothstein (MVP - Excel) helped me out here with a powerful user defined function (udf).

Array formula in cell B2:B23

This is how you enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Array formula in cell C2:C23

Select all the cells to be filled, then type the above formula  into the Formula Bar and press CTRL+SHIFT+ENTER

User defined function

Instructions

1. You can select far more cells to load the formulas in than are required by the list. The empty text string will be displayed for cells not having an entry.
2. You can specify a larger range than the there are filled in cells as the argument to these macros to allow for future entries in the column.
3. You can specify whether the listing is to be case sensitive or not via the optional second argument with the default value being FALSE, meaning duplicated entries with different casing like One, one, ONE, onE, etc.. will all be treated as if they were the same word with the same spelling. If you pass TRUE for that optional second argument, then those words would all be treated as if they were different words.
4. For all the "Case Insensitive" listing, the words are listed in Proper Case (first letter upper case, remaining letters lower case). The reason being if you had One, one and ONE then there is not reason to prefer one version over another, so I solved the problem by using Proper Case throughout.

VBA Code:

Function UniqueWords(Rng As Range, Optional  CaseSensitive As Boolean) As Variant
Dim X As Long, WordCount As Long,  List As String, Uniques As Variant, Words() As String
List =  WorksheetFunction.Trim(Replace(Join(WorksheetFunction.Transpose(Rng)), Chr(160),  " "))
Words = Split(List)
For X = 0 To  UBound(Words)
If CaseSensitive  Then
If UBound(Split(" " &; List & " ",  " " & Words(X) & " ")) = 1  Then
Uniques = Uniques &  Words(X) & " "
List =  Replace(List, Words(X), "")
End  If
Else
If  UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & "  ")) = 1 Then
Uniques = Uniques  & StrConv(Words(X), vbProperCase) & "  "
List = Replace(List, Words(X),  "")
End If
End  If
Next
Uniques = WorksheetFunction.Trim(Uniques)
Words = Split(Uniques)
If Application.Caller.Count > UBound(Words)  Then
Uniques = Uniques & Space(Application.Caller.Count - UBound(Words))
End If
UniqueWords = WorksheetFunction.Transpose(Split(Uniques))
End  Function

How to implement user defined function in excel

1. Press Alt-F11 to open visual basic editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste the above user defined function
4. Exit visual basic editor
5. Select a sheet
6. Select a cell range
7. Type =UniqueWords($A$2:$A$18, TRUE) into formula bar and press CTRL+SHIFT+ENTER

Get the Excel file

Unique-words-from-a-range.xls

Get Rick Rothstein's Excel example file

Get the Excel file

Word-Listing-Code1.xls

Many thanks to Rick Rothstein (Mvp - Excel)!!

2. Filter unique strings in a range - Excel 365

The image above demonstrates a formula in cell D3 that extracts unique strings based on the space character as a delimiting character. A unique string is a string that exists only once in a cell range.

Note, string "Us" is equal to "US", in other words, this is not a case-sensitive formula.

Excel 365 formula in cell D3:

Explaining formula

Step 1 - Join cell values

The TEXTJOIN function combines text strings from multiple cell ranges and also use delimiting characters if you want.

TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

TEXTJOIN(" ", TRUE, B3:B15)

returns

"3M - Asia 3M  South America 3M - Africa 3M - US 3M - ASIA 3M - Us 3M - South AMERICA 3M - Europe 3M  Australia 3M Australia 3M Europe 3M aSIA 3M US".

Step 2 - Split strings based on space as a delimiting character

The TEXTSPLIT function splits a string into an array across columns and rows based on delimiting characters.

TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

TEXTSPLIT(TEXTJOIN(" ", TRUE, B3:B15), , " ", TRUE, 0)

becomes

TEXTSPLIT("3M - Asia 3M  South America 3M - Africa 3M - US 3M - ASIA 3M - Us 3M - South AMERICA 3M - Europe 3M  Australia 3M Australia 3M Europe 3M aSIA 3M US", , " ", TRUE, 0)

and returns

{"3M"; "-"; "Asia"; "3M "; "South"; "America"; "3M"; "-"; "Africa"; "3M"; "-"; "US"; "3M"; "-"; "ASIA"; "3M"; "-"; "Us"; "3M"; "-"; "South"; "AMERICA"; "3M"; "-"; "Europe"; "3M "; "Australia"; "3M"; "Australia"; "3M"; "Europe"; "3M"; "aSIA"; "3M"; "US"}.

Step 3 - List unique strings

The UNIQUE function extracts both unique and unique distinct values and also compare columns to columns or rows to rows.

UNIQUE(array,[by_col],[exactly_once])

UNIQUE(TEXTSPLIT(TEXTJOIN(" ", TRUE, B3:B15), , " ", TRUE, 0), , TRUE)

becomes

UNIQUE({"3M"; "-"; "Asia"; "3M "; "South"; "America"; "3M"; "-"; "Africa"; "3M"; "-"; "US"; "3M"; "-"; "ASIA"; "3M"; "-"; "Us"; "3M"; "-"; "South"; "AMERICA"; "3M"; "-"; "Europe"; "3M "; "Australia"; "3M"; "Australia"; "3M"; "Europe"; "3M"; "aSIA"; "3M"; "US"}, , TRUE)

and returns "Africa".