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

"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

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

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

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

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

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

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