Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bagman
 
Posts: n/a
Default sort cells with hyphen #####-###-####

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

You can extract the 3 middle characters to a new column and then sort on that
new
column.

=MID(A1,7,3)

HTH
Jason
Atlanta, GA

"bagman" wrote:

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks

  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

Put the middle numbers into a separate column and sort by that.

This formula will get you those values

=LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"bagman" wrote in message
...
How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks



  #4   Report Post  
bagman
 
Posts: n/a
Default

Hello Jason,

Thanks..But I am not that good with excel..so could you explain how to
extract the by the three middle numbers and sort the complete number in a
new column.
Sorry for all the questions.

"Jason Morin" wrote:

You can extract the 3 middle characters to a new column and then sort on that
new
column.

=MID(A1,7,3)

HTH
Jason
Atlanta, GA

"bagman" wrote:

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks

  #5   Report Post  
bagman
 
Posts: n/a
Default

Hello Bob,

As I told Jason I am not very good with excel..I got your formula. But how
and where to I enter it get the column to sort in a new column.
Thanks


"bagman" wrote:

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks



  #6   Report Post  
Tom Ogilvy
 
Posts: n/a
Default

Bob's formula
=LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1)

would assume your data is in column A. So this would be placed in cell B1,
then copied down next to each entry in column A.

--
Regards,
Tom Ogilvy

"bagman" wrote in message
...
Hello Bob,

As I told Jason I am not very good with excel..I got your formula. But how
and where to I enter it get the column to sort in a new column.
Thanks


"bagman" wrote:

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks



  #7   Report Post  
bagman
 
Posts: n/a
Default

Thanks Tom, Bob and Jason,
That worked...

"Tom Ogilvy" wrote:

Bob's formula
=LEFT(MID(A1,FIND("-",A1)+1,99),FIND("-",MID(A1,FIND("-",A1)+1,99))-1)

would assume your data is in column A. So this would be placed in cell B1,
then copied down next to each entry in column A.

--
Regards,
Tom Ogilvy

"bagman" wrote in message
...
Hello Bob,

As I told Jason I am not very good with excel..I got your formula. But how
and where to I enter it get the column to sort in a new column.
Thanks


"bagman" wrote:

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers
Thanks




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
sort cells with hyphen #####-###-#### infurtaris Excel Discussion (Misc queries) 0 January 1st 05 06:15 PM
To safety merge cells without data destroyed, and smart unmerge! Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:17 AM
Heps to design Locked/Unlocked cells in protected worksheet Kevin Excel Discussion (Misc queries) 0 December 30th 04 08:09 AM
Convert data of cells to any type: Number, Date&Time, Text Kevin Excel Discussion (Misc queries) 0 December 30th 04 07:55 AM
Data > Sort function amnesia? Dave D Excel Discussion (Misc queries) 1 November 29th 04 11:44 PM


All times are GMT +1. The time now is 02:30 PM.

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

About Us

"It's about Microsoft Excel"