Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple numbers in a cell separated by a comma, need separate cel | Excel Discussion (Misc queries) | |||
I open a CSV file with excel, but the text is separate with comma! | Excel Discussion (Misc queries) | |||
separate whole words | Excel Worksheet Functions | |||
how to separate words between blanks | Excel Discussion (Misc queries) | |||
MSExcel--words in a cell separated by comma need to be moved into. | Excel Worksheet Functions |