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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

I reallly need your help. I'm working on various lenghty documents and I need an easier way to find/replace ##### and not go through 500 pages cell by cell.

Can anyone help?

Thanks, Leah