How to separate words by comma

  • Thread starter Thread starter Rowan Drummond
  • Start date Start date
R

Rowan Drummond

Make sure that the columns right of your data are empty. Then select all
the data in column A and from the menus select Data>TextToColumns. On
the first screen select Delimited and then click Next. On the second
screen click in the Comma check box and then click finish.

Hope this helps
Rowan
 
Hi everyone,

Can I separate several words that is joined by comma?
for example:
Column A --> word1, word2, word3, word4

Is there any way to make it
Column B --> word1
Column C --> word2
Column D --> word3
Column E --> word4

Thank you very much. I really really appreciate your help.
 
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, 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.
 
Back
Top