I have a list like this in column A:

cell A1. CHHKSHANAN000021,CHHKSHASAV001873,CHHKSHAOCF000026
cell A2. APLU020852965,APLU020852976,APLU020852966,APLU0208 52977


....but much longer. I want the data in separate cell (i.e., a1, a2, a3):

CHHKSHANAN000021
CHHKSHASAV001873
CHHKSHAOCF000026

I had tried the convert text to column wizard, however the data display only
horizontally. Is there a formula I can use to undo the Concatenated cells at
the same time insert the line to post each item?

Please help!

--
Thank you,

It can be easily, done with VBA Macro - but if you prefer formulas then:
Assuming you have a string with 5 sections [as pictured]
1) The first {a2} and last [A6] formulas are different.
2) Formulas for Sections 2 till n-1 [2-4 in my example] are "identical.
*** The one in cell A3 should be and copied down till A5
http://img175.imageshack.us/img175/1418/nonamec.png
Micky


"Kitty" wrote:

I have a list like this in column A:

cell A1. CHHKSHANAN000021,CHHKSHASAV001873,CHHKSHAOCF000026
cell A2. APLU020852965,APLU020852976,APLU020852966,APLU0208 52977


...but much longer. I want the data in separate cell (i.e., a1, a2, a3):

CHHKSHANAN000021
CHHKSHASAV001873
CHHKSHAOCF000026

I had tried the convert text to column wizard, however the data display only
horizontally. Is there a formula I can use to undo the Concatenated cells at
the same time insert the line to post each item?

Please help!

--
Thank you,

Micky,

How would you do it with VBA Macro.

if each cell in cloumn B includes multiple number values, separated by comma
and I would like to create a new row for each unique number in column B such
that there remains only one unique value for every B cell.

--
Thank you,




"מיכ�ל (מיקי) �בידן" wrote:

It can be easily, done with VBA Macro - but if you prefer formulas then:
Assuming you have a string with 5 sections [as pictured]
1) The first {a2} and last [A6] formulas are different.
2) Formulas for Sections 2 till n-1 [2-4 in my example] are "identical.
*** The one in cell A3 should be and copied down till A5
http://img175.imageshack.us/img175/1418/nonamec.png
Micky


"Kitty" wrote:

I have a list like this in column A:

cell A1. CHHKSHANAN000021,CHHKSHASAV001873,CHHKSHAOCF000026
cell A2. APLU020852965,APLU020852976,APLU020852966,APLU0208 52977


...but much longer. I want the data in separate cell (i.e., a1, a2, a3):

CHHKSHANAN000021
CHHKSHASAV001873
CHHKSHAOCF000026

I had tried the convert text to column wizard, however the data display only
horizontally. Is there a formula I can use to undo the Concatenated cells at
the same time insert the line to post each item?

Please help!

--
Thank you,