Excel - Macro Required - Pick full words from text in a cell 1

[Kevsim]

With the following text dumped in a single cell " This is a test for placing each word in a seperate cell " (without quote marks) I want to start from the front and pick out "This" as a full word then place the word in a cell. Then pick the next full word in the sentence and place it next to the first cell, then so on until the last word ' cell', each word is now in a seperate cell.
It would then go down to the next row, which has a different sentence and pick the words.
This would continue until there are no more sentences.
I would appreciate your advise.
kevsim

 

[dsi]

If your sentances are in column A, and you want the words split into successive columns in the same row, try the following routine:

Option Explicit

Sub SplitSentances()
    Dim iRow As Long, iCol As Long, iPos As Integer
    Dim sSent As String, sWord As String
    
    iRow = 1    'first row of sentances
    Do While Len(Range("A" & iRow).Text) > 0
        iCol = 1
        sSent = Cells(iRow, iCol).Text
        iPos = InStr(1, sSent, " ")
        Do While iPos > 0
            iCol = iCol + 1
            sWord = Left(sSent, iPos - 1)
            sSent = Right(sSent, Len(sSent) - iPos)
            Cells(iRow, iCol).Value = sWord
            iPos = InStr(1, sSent, " ")
        Loop
        'place the last word in the sentance
        iCol = iCol + 1
        Cells(iRow, iCol).Value = sSent
        iRow = iRow + 1
    Loop
End Sub

Hope this helps!