How to sort data WITHIN a cell in Microsoft Excel 2007 on Windows?
March 5, 2011 1:46 PM Subscribe
How to sort data WITHIN a cell in Microsoft Excel 2007 on Windows?
I have row after row of data, some of which has multiple lines of text in a single cell. I'd like to sort the data within that cell in alphabetical order.
What's the best solution for doing this?
Some desired capabilities of the solution:
As this is scientific data I need to be sure that the sort does not lose any of its contents if the sort function chokes.
Only one column of the spreadsheet is to be internally sorted.
It would be nice if duplicates would be minimized, meaning if there are two lines with the same data in a cell, they would be turned into one line
Free is best, but I don't mind paying
For Excel 2007 running under Windows
If a binary, from a trustworthy site.
If it could do all cells in a column at once that would be great, but if I had to do it one cell at a time that's okay.
Easy to run as I may be doing this 500 times per worksheet
the data is a list of strings that look like file paths e.g. /a/b/c/d
I have row after row of data, some of which has multiple lines of text in a single cell. I'd like to sort the data within that cell in alphabetical order.
What's the best solution for doing this?
Some desired capabilities of the solution:
As this is scientific data I need to be sure that the sort does not lose any of its contents if the sort function chokes.
Only one column of the spreadsheet is to be internally sorted.
It would be nice if duplicates would be minimized, meaning if there are two lines with the same data in a cell, they would be turned into one line
Free is best, but I don't mind paying
For Excel 2007 running under Windows
If a binary, from a trustworthy site.
If it could do all cells in a column at once that would be great, but if I had to do it one cell at a time that's okay.
Easy to run as I may be doing this 500 times per worksheet
the data is a list of strings that look like file paths e.g. /a/b/c/d
Without seeing the exact data layout, I am not certain if this would help. But, you could use data > text to columns and use the / as a delimiter to break the cells into different columns, transpose the data from rows to columns and then sort.
The trick would vary depending on the data and the needs of the column. If there are lots of other columns it could be worth it to seperate that column into its own sheet (leaving the original) and later do a concataneted vlookup to re-attach the data.
posted by occidental at 3:43 PM on March 5, 2011
The trick would vary depending on the data and the needs of the column. If there are lots of other columns it could be worth it to seperate that column into its own sheet (leaving the original) and later do a concataneted vlookup to re-attach the data.
posted by occidental at 3:43 PM on March 5, 2011
Can you provide a sample via email? I might have a solution but would need to test it. The issue is the data inside the cell that cannot be sorted by normal means. However, with care, it may be possible to extract that column, use external apps to sort, then put the data back into the appropriate cells.
posted by lampshade at 8:34 AM on March 6, 2011
posted by lampshade at 8:34 AM on March 6, 2011
Response by poster: The data that is in the cell is a series of strings like this
/foo/bar/baz.g
/zzz/laaa/sing
/432/dfed.564/ddd4.hhh
/foo/bar/baz.g
posted by bottlebrushtree at 10:32 AM on March 6, 2011
/foo/bar/baz.g
/zzz/laaa/sing
/432/dfed.564/ddd4.hhh
/foo/bar/baz.g
posted by bottlebrushtree at 10:32 AM on March 6, 2011
Is the "/" actually in the cell or is it separated by a line?
posted by lampshade at 10:34 AM on March 6, 2011
posted by lampshade at 10:34 AM on March 6, 2011
If it's actually 3 in each, I'd consider breaking it in to three cells.
posted by maryr at 12:36 PM on March 6, 2011
posted by maryr at 12:36 PM on March 6, 2011
Response by poster: Each cell contains data blocks like what I pasted in above.
The length of each path item is variable e.g. could be
foo
or
/a/bb/ccc/ddd/eee/fff/ggg
and there could be anywhere from 1 to 200 lines "per cell"
posted by bottlebrushtree at 5:51 PM on March 6, 2011
The length of each path item is variable e.g. could be
foo
or
/a/bb/ccc/ddd/eee/fff/ggg
and there could be anywhere from 1 to 200 lines "per cell"
posted by bottlebrushtree at 5:51 PM on March 6, 2011
« Older Is there free software I can use to do camp... | Help me find the right web app for my project. Newer »
This thread is closed to new comments.
posted by ~Sushma~ at 1:53 PM on March 5, 2011 [1 favorite]