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.

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
>
>

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

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.
>
>

Data>Text to Columns>Deleimited by comma>Finish


Gord Dibben Excel MVP

On Thu, 15 Dec 2005 17:32:50 -0600, wliong
<wliong.203uka_1134689702.7021@excelforum-nospam.com> 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.

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).

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.

"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
>
>