Splitting Data in a Cell
 

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??

Look at datatext to columns, delimted and space as delimiter


Regards,

Peo Sjoblom

"Newbie1092" wrote:

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??

Thanks for the reply and I know that would work but like I said I can not use
that feature because that feature requires there be data in the cells prior
to using the feature. This is a template worksheet and the data will be
pasted into the cells at a later date but different individuals. I would
prefer to use some type of formular rather than teach people how to apply
that function to possible upto 1000 different cells.



"Peo Sjoblom" wrote:

Look at datatext to columns, delimted and space as delimiter


Regards,

Peo Sjoblom

"Newbie1092" wrote:

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??

Sorry, I should have read your post more thoroughly. However, there is no "In
one fell swoop formula" since you say there can be cells with one value and
others with multiple values. Besides you would need several formulas
(basically one hundred formulas in some cases), You could count the spaces
and based on that design your formulas but it would be a hellish task and
nasty to audit. You should probably go with VBA but as I said it is not a
simple task

Anyway here is a link to a formula example where you can use space to
extract any data

http://tinyurl.com/cs5vs

the same technique can be adapted to extract and occurrence of a word but it
will take a long time if you want to use formulas



Regards,

Peo Sjoblom

"Newbie1092" wrote:

Thanks for the reply and I know that would work but like I said I can not use
that feature because that feature requires there be data in the cells prior
to using the feature. This is a template worksheet and the data will be
pasted into the cells at a later date but different individuals. I would
prefer to use some type of formular rather than teach people how to apply
that function to possible upto 1000 different cells.



"Peo Sjoblom" wrote:

Look at datatext to columns, delimted and space as delimiter


Regards,

Peo Sjoblom

"Newbie1092" wrote:

I have a template work sheet that contains several cells that hold multiple
pieces of data. One of the things that I have to do with each cell is sort
the data in the cell into alphabetical order. I understand I have to first
break the data out into individual cells and then sort thos cells. My issue
is the template needs to be set up so that the data can be pasted from a .csv
file so Iu do not have the ability to use the "text to columns" feature. I
need the formula to split this data already set up and ready to go. An
example of the data is as follows:

CII CTI DDI DE TEUP BIU2 XWUU AAU

There can be as few is 1 data item or as many as 100 in each cell. Then
going down the column there can be as few as 1 cells in the column or as many
as 1000 cells. Each cell should be treated as an individual issue and there
is plenty of room to the right for breaking the cell out.

Does anyone have any suggestions??