Sort data separated by commas

D

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
 
B

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.
 
G

Glenn

dab said:
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))
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top