Leith, I am trying this exact thing but for 600 rows, is there anything to
automate the run, and I did as you suggested and nothing happened after
selecting the macro. My only difference in the macro is it is space
delimited not comma, I took out your comma reference and put in a space" " .
Do you have any more help on this subject, I am dieing to hear it.

"Leith Ross" wrote:


Hello Wliong.

Here is a macro that will prompt you for the cell that contains the
text separated by commas. It will then place the words in the same row
starting one column to the right. Insert a module into your porject and
paste this code in. You can then call the macro from the Macros list (by
pressing ALT + F8 while in Excel).


Code:
--------------------

Sub SplitWords()

Dim Answer
Dim Col As Long
Dim N As Long
Dim RetVal
Dim Row As Long
Dim Rng As Range

Answer = InputBox("Enter the cell address with the comma separated text.")
If Answer = "" Then Exit Sub

On Error Resume Next
Set Rng = ActiveSheet.Range(Answer)
If Err.Number < 0 Then
RetVal = MsgBox(Answer & " is not a valid Cell Address.", vbExclamation + vbOKOnly)
Exit Sub
End If

With Rng
Col = .Column
Row = .Row
End With

Text = Split(Rng.Value, ",")

With ActiveSheet
For Each Word In Text
N = N + 1
.Cells(Row, Col + N).Value = Word
Next Word
End With

End Sub

--------------------



Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=493982