Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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?? |
#2
|
|||
|
|||
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?? |
#3
|
|||
|
|||
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?? |
#4
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
up to 7 functions? | Excel Worksheet Functions | |||
Bringing partial data from one cell into another | Excel Worksheet Functions | |||
Import Data into same cell | Excel Discussion (Misc queries) | |||
I want a window to cell data that conains links to PDF files and . | Excel Discussion (Misc queries) | |||
Repeat Cell Data | Excel Worksheet Functions |