SEPERATE NUMBERS IN A CELL

L

lehigh46

Hi All,

I have copied a small sample of a spread sheet which contains the
following numbers and they resides in column A.

 562111
 541512
 42149, 42184, 42212, 42213
 323122, 323114, 541922
 541512, 541519
 423810, 424990, 423820, 424690, 532412
 236210, 236220, 237110, 237310, 237990, 541330, 541380
 56291, 56121, 23816, 23891
 54191, 541611, 54163
 524210
 541614

I would like to seperate the numbers into columns B, C, D, E, and so
on, starting with the first number in column B.

Notice that the numbers are not all the same length.

Can anyone tell me how I can accomplish this?



Thanks for your help


Tom Snyder






x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 100,000 other groups
x-- Access to over 1.6 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
 
L

lehigh46

Thanks for the help. It worked fine.

There is one problem,...... and I think it has something to do with
formatting.

When I seprated the numbers, I noticed that the first number (which
remained in it's original position) has a leading space which was
existing when I copied and pasted from a website.
This will not allow it to act numericly. The rest of the numbers are
OK.

The original info looked like the following;

NAICS Codes: 562111
NAICS Codes: 541512
NAICS Codes: 42149, 42184, 42212, 42213
NAICS Codes: 323122, 323114, 541922
NAICS Codes: 541512, 541519
NAICS Codes: 423810, 424990, 423820, 424690, 532412
NAICS Codes: 236210, 236220, 237110, 237310, 237990, 541330, 541380
NAICS Codes: 56291, 56121, 23816, 23891
NAICS Codes: 54191, 541611, 54163
NAICS Codes: 524210
NAICS Codes: 541614

I then did a find and replace

FIND: NAICS Codes:
REPALCE ALL: (I left it blank)

This resulted in the numbers you see below.


Is there any way that I can fix this?


Thanks Again


Tom Snyder









Data/Text to Columns/Delimited/Comma
Click to expand...



x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 100,000 other groups
x-- Access to over 1.6 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
 
J

JE McGimpsey

Format the column as General. Copy an empty cell. Select the column.
Choose Edit/Paste Special, selecting the Values and Add radio buttons.
Click OK.

This coerces "text numbers" to real numbers.
 
L

lehigh46

It didn't work.

When I pasted it gave me an empty column.
It must still think it's alpha.






Format the column as General. Copy an empty cell. Select the column.
Choose Edit/Paste Special, selecting the Values and Add radio buttons.
Click OK.

This coerces "text numbers" to real numbers.
Click to expand...



x-- 100 Proof News - http://www.100ProofNews.com
x-- 3,500+ Binary NewsGroups, and over 100,000 other groups
x-- Access to over 1.6 Terabytes per Day - $8.95/Month
x-- UNLIMITED DOWNLOAD
 
J

JE McGimpsey

If it gave you an empty column, you didn't Paste Special with the Add
radio button (as well as the Values radio button) selected.
 
G

Guest

as long as a space is always used as your data separator
select your data
<data><Text to columns> delimited space
 
G

Guest

Hi,
Select the cells.
Click 'Data' (in the Toolbar) --> Text to Columns --> Next --> check the
'Comma' box -->Next -->Finish
Regards,
B.R.Ramachandran
 
G

Guest

Hi Tom, you need to use Data > Text to Columns wizard. Use "delimited" and
assign the comma as the seperator, this will automatically split the data at
the commas and push them over in the columns as far as they need to go for
each cell.

HTH,

Kevin M
MOS Excel Specialist
 
Top