Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split text in a column to multiple columns
Can I split text in a column like the example below into
three columns? Column = Column1 Column2 Column3 1 1234 John W. Doe = 1 1234 John W. Doe Thanks, milo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split text in a column to multiple columns
Okay this isn't the most elegant code, but I'm still learning, and will
always welcome tips on way to make my code more efficient. This should work assuming your data is in column A. Columns("B:E").Select Selection.insert Shift:=xlToRight Columns("A:A").Select Application.DisplayAlerts = False Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _ Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)) Application.DisplayAlerts = True Columns("C:C").Select Selection.insert Shift:=xlToRight Range("C1").Select Do ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[2],"" "",RC[3])" ActiveCell.Offset(1, 0).Select Loop Until ActiveCell.Offset(0, -2) = "" Range("c:C").Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Columns("D:F").Select Selection.delete Shift:=xlToLeft ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split text in a column to multiple columns
the easiest way is to highlight all the colums and then go
to the data menu and go to 'text to columns' and then hit next and put the line where you want to seperate them. -----Original Message----- Can I split text in a column like the example below into three columns? Column = Column1 Column2 Column3 1 1234 John W. Doe = 1 1234 John W. Doe Thanks, milo . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split text into multiple columns using a common delimiter | Excel Discussion (Misc queries) | |||
split one column to multiple columns | Excel Worksheet Functions | |||
split one column into multiple columns | Excel Discussion (Misc queries) | |||
Split numbers and text from 1 column into 2 columns | Excel Discussion (Misc queries) | |||
text from one column into multiple columns | Excel Discussion (Misc queries) |