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

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

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

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

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

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

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