How to separate words by comma
 

Make sure that the columns right of your data are empty. Then select all
the data in column A and from the menus select DataTextToColumns. 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

wliong wrote:
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.

How to separate words by comma
 

Without code look at Data - Text To Columns

With Code look at the Split Function...
--
HTH...

Jim Thomlinson


"wliong" wrote:


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.


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

How to separate words by comma
 

DataText to ColumnsDeleimited by commaFinish


Gord Dibben Excel MVP

On Thu, 15 Dec 2005 17:32:50 -0600, wliong
wrote:


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.

How to separate words by comma
 

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.


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

How to separate words by comma
 

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

How to separate words by comma
 

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