Splitting text to words

an example spreadsheet with what you have and what you want would be nice.

Here you go

Split text to words.xlsx

Text to columns will do the splitting for you
https://www.spreadsheetsmadeeasy.com/how-to-split-cells-in-excel/

Did you check out the Words sheet?

The unique words will be put in column A using the following:

Sub WordMaster()
Dim v As Variant, vSplit As Variant, vWords As Variant, vTitles As Variant
Dim dic As Object
Dim wsWords As Worksheet
Dim rgTitles As Range, rgWords As Range
Dim i As Long, j As Long, k As Long, nTitles As Long, nWords As Long

Set dic = CreateObject("Scripting.Dictionary")
With ActiveSheet
    Set rgTitles = Intersect(.UsedRange, .Columns("C"))
End With

On Error Resume Next
Set wsWords = Worksheets("Words")
If wsWords Is Nothing Then
    Set wsWords = Worksheets.Add(After:=ActiveSheet)
    wsWords.Name = "Words"
End If

wsWords.Columns("A").ClearContents

vTitles = rgTitles.Value
nTitles = UBound(vTitles)

For i = 1 To nTitles
    If vTitles(i, 1) <> "" Then
        vSplit = Split(vTitles(i, 1), " ")
        For Each v In vSplit
            If Not dic.exists(v) Then
                j = j + 1
                dic.Add v, j
            End If
        Next
    End If
Next
On Error GoTo 0

nWords = j
ReDim vWords(1 To nWords)
For j = 1 To nWords
    vWords(j) = dic.Keys()(j - 1)
Next

wsWords.Range("A1").Resize(nWords, 1).Value = Application.Transpose(vWords)
End Sub

Select allOpen in new window

Split-text-to-words.xlsm

Hi Saquib,

You may alos try something like this....

Sub getWords()
Dim wsData      As Worksheet
Dim wsWords     As Worksheet
Dim LR          As Long
Dim i           As Long
Dim j           As Long
Dim x           As Variant
Dim arrWords    As Variant
Dim word        As Variant
Dim dict        As Object


Application.ScreenUpdating = False


Set wsData = Worksheets("Sheet1")


On Error Resume Next
Set wsWords = Worksheets("Words")
wsWords.Cells.Clear
On Error GoTo 0


If wsWords Is Nothing Then
    Set wsWords = Worksheets.Add(after:=wsData)
    wsWords.Name = "Words"
End If


LR = wsData.Cells(Rows.Count, "C").End(xlUp).Row
x = wsData.Range("C2:C" & LR).Value


Set dict = CreateObject("Scripting.Dictionary")
ReDim arrWords(1 To 1048576, 1 To 1)


For i = 1 To UBound(x, 1)
    If x(i, 1) <> "" Then
        For Each word In Split(x(i, 1), " ")
            If Not dict.exists(word) Then
                j = j + 1
                dict.Item(word) = ""
                arrWords(j, 1) = word
            End If
        Next word
    End If
Next i
wsWords.Range("A1").Resize(j, 1) = arrWords


Application.ScreenUpdating = True
End Sub

Select allOpen in new window

Split-text-to-words.xlsm

Thanks both.

Both work but are case sensitive, thus I am getting duplicates. Need to compare case insensitive.

It would be great if you could also add a second column giving the count of each word.

Hi, both.

Thanks a lot. Both codes worked well as requested. Brad's code had an additional feature to handle the punctuations which was very helpful. I had to add parenthesis to it too. 

You're welcome Saqib!