Sort data separated by commas
 

Good day and thanks for reading.
I am working on a file where a cell contains data separated by a comma.
ex:
Col A Col B
Row 1 {Medium, Blue, hard } { 153 }
Row 2 {Big, Red, soft } { 230 }
Row 3 {Small, Pink, hard } { 50 }
Row 5 {Big, Blue, soft } { 250 }

Where { } are the limits of the cell

What I am looking for is a way to sort Column A by the second value on each
cell, ex: (Blue, Red, Pink)

Any help is greatly appreciated.

thanks

Sort data separated by commas
 

What I would to is use the "text to columns" feature of excel (found in the
menu bar or Ribbon depending if you are in xl03 or el07) - split the cell
into three cells and sort on the middle cell.

If you still want to keep the information the way you have it - copy it to
the end - then do the "test to columns" - delete what you don't need and then
sort on the remaining column.

--
Wag more, bark less


"dab" wrote:

Good day and thanks for reading.
I am working on a file where a cell contains data separated by a comma.
ex:
Col A Col B
Row 1 {Medium, Blue, hard } { 153 }
Row 2 {Big, Red, soft } { 230 }
Row 3 {Small, Pink, hard } { 50 }
Row 5 {Big, Blue, soft } { 250 }

Where { } are the limits of the cell

What I am looking for is a way to sort Column A by the second value on each
cell, ex: (Blue, Red, Pink)

Any help is greatly appreciated.

thanks

Sort data separated by commas
 

dab wrote:
Good day and thanks for reading.
I am working on a file where a cell contains data separated by a comma.
ex:
Col A Col B
Row 1 {Medium, Blue, hard } { 153 }
Row 2 {Big, Red, soft } { 230 }
Row 3 {Small, Pink, hard } { 50 }
Row 5 {Big, Blue, soft } { 250 }

Where { } are the limits of the cell

What I am looking for is a way to sort Column A by the second value on each
cell, ex: (Blue, Red, Pink)

Any help is greatly appreciated.

thanks




This will result in the second value in a new column, which you an use to sort:

=TRIM(MID(SUBSTITUTE(A1,", ",REPT(" ",99)),99,99))

Sort data separated by commas
 

Hi,

Extract the middle word into a helper column and then select all 3 columns
and sort on the helper column. You can hide this helper column if you want.
Use this formula to extract the middle word. I've left the comma in but that
won't affect things

=TRIM(MID(SUBSTITUTE(" " & A1&REPT(" ",6)," ",REPT(" ",255)),2*255,255))


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"dab" wrote:

Good day and thanks for reading.
I am working on a file where a cell contains data separated by a comma.
ex:
Col A Col B
Row 1 {Medium, Blue, hard } { 153 }
Row 2 {Big, Red, soft } { 230 }
Row 3 {Small, Pink, hard } { 50 }
Row 5 {Big, Blue, soft } { 250 }

Where { } are the limits of the cell

What I am looking for is a way to sort Column A by the second value on each
cell, ex: (Blue, Red, Pink)

Any help is greatly appreciated.

thanks

Sort data separated by commas
 

Hi,

Download and install the following addin -
http://www.download.com/Morefunc/300...-10423159.html. Suppose your
data is in range A1:B5 (row 1 has the headings). Select F2:G5 and then use
the following array formula (Ctrl+Shift+Enter)

=VSORT(A2:B5,TRIM(MID(SUBSTITUTE(A2:A5,", ",REPT(" ",99)),99,99)),1)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"dab" wrote in message
...
Good day and thanks for reading.
I am working on a file where a cell contains data separated by a comma.
ex:
Col A Col B
Row 1 {Medium, Blue, hard } { 153 }
Row 2 {Big, Red, soft } { 230 }
Row 3 {Small, Pink, hard } { 50 }
Row 5 {Big, Blue, soft } { 250 }

Where { } are the limits of the cell

What I am looking for is a way to sort Column A by the second value on
each
cell, ex: (Blue, Red, Pink)

Any help is greatly appreciated.

thanks