Sort data separated by commas

[dab]

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

 

[Brad]

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.

 

[Glenn]

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))

 

[Mike H]

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.

 

[Ashish Mathur]

Hi,

Download and install the following addin -
http://www.download.com/Morefunc/3000-2077_4-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