A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Sort data separated by commas



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 09:43 PM posted to microsoft.public.excel.worksheet.functions
DAB
external usenet poster
 
Posts: 31
Default 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


  #2  
Old May 13th, 2010, 10:00 PM posted to microsoft.public.excel.worksheet.functions
Brad
external usenet poster
 
Posts: 943
Default 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


  #3  
Old May 13th, 2010, 10:05 PM posted to microsoft.public.excel.worksheet.functions
Glenn[_6_]
external usenet poster
 
Posts: 1,245
Default 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))
  #4  
Old May 13th, 2010, 10:08 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default 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


  #5  
Old May 14th, 2010, 12:53 AM posted to microsoft.public.excel.worksheet.functions
Ashish Mathur[_2_]
external usenet poster
 
Posts: 1,764
Default 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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:38 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.