VBA has a function named SPLIT which does what you want. The syntax for the
function is SPLIT(String value, Delimiter)

If you place a sentence in column A row 1 of Sheet 1, and place another in
column a row 2, you can run the following code to parse the words separated
by spaces and place them on sheet 2 column A in a column. Hope it helps to
point you in the general direction.

Sub SplitSentences()

Dim wb As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim l As Long
Dim strSentence As String
Dim varArray As Variant
Dim varItems As Variant

Set wb = ActiveWorkbook
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")

ws1.Activate
Range("A1").Select
strSentence = ActiveCell.Value

Do Until strSentence = ""
ws2.Activate
Range("A1").Select
varArray = Split(strSentence, " ")
varItems = varArray
For Each varItems In varArray
ActiveCell.Offset(l).Value = varItems
l = l + 1
Next varItems
ws1.Activate
ActiveCell.Offset(1).Select
strSentence = ActiveCell.Value
Loop

Set wb = Nothing
Set ws1 = Nothing
Set ws2 = Nothing

--
Kevin Backmann


"sparx" wrote:


Does anybody know of a formula that can look at a sentence of say 3 or 4
words and then look for the spaces inbetween the words and put each word
in a different cell underneath each other?


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=515292