Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
CiceroCF
 
Posts: n/a
Default 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   Report Post  
Fredrik Wahlgren
 
Posts: n/a
Default


"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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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   Report Post  
CiceroCF
 
Posts: n/a
Default

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   Report Post  
CiceroCF
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Occurance of data in multiple columns Jerry Excel Worksheet Functions 1 March 12th 05 03:22 PM
How do i copy columns of data in notepad into microsoft excel? Jason Excel Discussion (Misc queries) 1 February 10th 05 10:20 PM
merge data from multiple columns to single column triggerthehorse Excel Worksheet Functions 2 January 17th 05 08:19 PM
Data from a number of rows and columns to one column mark_h82 Excel Discussion (Misc queries) 2 January 15th 05 04:02 AM
How do I link columns so data flows from 1 column to another like. M. Frazel Excel Discussion (Misc queries) 1 January 14th 05 05:17 PM


All times are GMT +1. The time now is 07:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"