Home |
Search |
Today's Posts |
#1
|
|||
|
|||
spliting a column of data into multiple columns
I am trying to split up some data into multiple columns with part of the data
in each column... ie: 02-34-5-M is currently in one column... i need it split into 4 columns as the following.... 02 34 5 M 01 256 0 Q 7 45 4 T This needs to be done so I can sort by using the 2nd set of numbers. Currently, this is an Excel file. Thanks in advance for your help. Carl |
#2
|
|||
|
|||
"CiceroCF" wrote in message ... I am trying to split up some data into multiple columns with part of the data in each column... ie: 02-34-5-M is currently in one column... i need it split into 4 columns as the following.... 02 34 5 M 01 256 0 Q 7 45 4 T This needs to be done so I can sort by using the 2nd set of numbers. Currently, this is an Excel file. Thanks in advance for your help. Carl Assuming 02-34-5-M is in A1, use =LEFT(A1,2) -02 =MID(A1,4,2) -34 =MID(A1,7,1) -5 =RIGHT(A1,1) -M /Fredrik |
#3
|
|||
|
|||
One way ..
Select the col, say col A ? Click Data Text to columns (Delimited will be selected) Click Next, and enter a dash in the box for "Other" Click Finish This will split the source col into 4 cols And if you need to retain the appearance of the leading zeros for the first col's numbers, just select the col and format as Custom, type: 00 -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "CiceroCF" wrote in message ... I am trying to split up some data into multiple columns with part of the data in each column... ie: 02-34-5-M is currently in one column... i need it split into 4 columns as the following.... 02 34 5 M 01 256 0 Q 7 45 4 T This needs to be done so I can sort by using the 2nd set of numbers. Currently, this is an Excel file. Thanks in advance for your help. Carl |
#4
|
|||
|
|||
DataText to ColumnsDelimited by "other" and enter a hyphen.
Make sure you have 3 empty columns to the right of the data. Gord Dibben Excel MVP On Thu, 24 Mar 2005 15:19:05 -0800, "CiceroCF" wrote: I am trying to split up some data into multiple columns with part of the data in each column... ie: 02-34-5-M is currently in one column... i need it split into 4 columns as the following.... 02 34 5 M 01 256 0 Q 7 45 4 T This needs to be done so I can sort by using the 2nd set of numbers. Currently, this is an Excel file. Thanks in advance for your help. Carl |
#5
|
|||
|
|||
small clarification ..
.. enter a dash in the box for "Other" dash means: "-" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#6
|
|||
|
|||
This worked with no problems....
Thank you for your help.... Carl "Max" wrote: small clarification .. .. enter a dash in the box for "Other" dash means: "-" -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
#7
|
|||
|
|||
Thanks.. this is what worked for me....
appreciate your help Cal "Gord Dibben" wrote: DataText to ColumnsDelimited by "other" and enter a hyphen. Make sure you have 3 empty columns to the right of the data. Gord Dibben Excel MVP On Thu, 24 Mar 2005 15:19:05 -0800, "CiceroCF" wrote: I am trying to split up some data into multiple columns with part of the data in each column... ie: 02-34-5-M is currently in one column... i need it split into 4 columns as the following.... 02 34 5 M 01 256 0 Q 7 45 4 T This needs to be done so I can sort by using the 2nd set of numbers. Currently, this is an Excel file. Thanks in advance for your help. Carl |
#8
|
|||
|
|||
Glad to hear that !
Thanks for the feedback -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "CiceroCF" wrote in message ... This worked with no problems.... Thank you for your help.... Carl |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Occurance of data in multiple columns | Excel Worksheet Functions | |||
How do i copy columns of data in notepad into microsoft excel? | Excel Discussion (Misc queries) | |||
merge data from multiple columns to single column | Excel Worksheet Functions | |||
Data from a number of rows and columns to one column | Excel Discussion (Misc queries) | |||
How do I link columns so data flows from 1 column to another like. | Excel Discussion (Misc queries) |