Alphanumeric Sort

I would like to know if there is a way to do an alphanumeric sort on some
data. I have item numbers like:
100
100A
200
300XX

A current sort returns
100
200
100A
300XX

Any Ideas?
--
Ray Rohde

no can do.
100a is not a number as excel sees it. it's text
excel sort numbers and text differently and when sorted
togeather, number first, text last.
built in. hard coded.
if you set all of your other numbers to text, you might
get the sort you are after. otherwise you might have to
use a helper column.
good luck
HDR
>-----Original Message-----
>I would like to know if there is a way to do an
alphanumeric sort on some
>data. I have item numbers like:
>100
>100A
>200
>300XX
>
>A current sort returns
>100
>200
>100A
>300XX
>
>Any Ideas?
>--
>Ray Rohde
>.
>

Ray wrote:
> I would like to know if there is a way to do an alphanumeric sort on some
> data. I have item numbers like:
> 100
> 100A
> 200
> 300XX
>
> A current sort returns
> 100
> 200
> 100A
> 300XX
>
> Any Ideas?

Is this the sort order you're trying to get to?

100
100A
200
300XX

The sort you showed is apparently the result of your column being some
numbers and some text mixed in together. Make all the cells text and it
sorts into the order that I've shown above.

If you have a few cells you can edit them and place a ' at the beginning
of the cell to force it to text.

If you've got a bunch of them then you might create another column:

[ ] = TEXT(A1,"########")

This column will be text and you can either sort on that, or copy the
new column and PasteSpecial/Values over top of the original data to
force it to become text.

Good luck....

Bill

Bill,

That is exactly what I was looking for. Thank you very much for your help

Ray

"Bill Martin -- (Remove NOSPAM from addre" wrote:

> Ray wrote:
> > I would like to know if there is a way to do an alphanumeric sort on some
> > data. I have item numbers like:
> > 100
> > 100A
> > 200
> > 300XX
> >
> > A current sort returns
> > 100
> > 200
> > 100A
> > 300XX
> >
> > Any Ideas?
>
> Is this the sort order you're trying to get to?
>
> 100
> 100A
> 200
> 300XX
>
> The sort you showed is apparently the result of your column being some
> numbers and some text mixed in together. Make all the cells text and it
> sorts into the order that I've shown above.
>
> If you have a few cells you can edit them and place a ' at the beginning
> of the cell to force it to text.
>
> If you've got a bunch of them then you might create another column:
>
> [ ] = TEXT(A1,"########")
>
> This column will be text and you can either sort on that, or copy the
> new column and PasteSpecial/Values over top of the original data to
> force it to become text.
>
> Good luck....
>
> Bill
>
>

Been beating my head against sorting mixed item numbers for about 2 hours
myself; got the alphanumeric mix converted to text but that creates a new
problem in that 121 sorts before 12, so I'm no better off. Idea?

"Bill Martin -- (Remove NOSPAM from addre" wrote:

> Ray wrote:
> > I would like to know if there is a way to do an alphanumeric sort on some
> > data. I have item numbers like:
> > 100
> > 100A
> > 200
> > 300XX
> >
> > A current sort returns
> > 100
> > 200
> > 100A
> > 300XX
> >
> > Any Ideas?
>
> Is this the sort order you're trying to get to?
>
> 100
> 100A
> 200
> 300XX
>
> The sort you showed is apparently the result of your column being some
> numbers and some text mixed in together. Make all the cells text and it
> sorts into the order that I've shown above.
>
> If you have a few cells you can edit them and place a ' at the beginning
> of the cell to force it to text.
>
> If you've got a bunch of them then you might create another column:
>
> [ ] = TEXT(A1,"########")
>
> This column will be text and you can either sort on that, or copy the
> new column and PasteSpecial/Values over top of the original data to
> force it to become text.
>
> Good luck....
>
> Bill
>
>

Breaking up the data into two columns will accomplish this task

If you add two helper columns to the left of your data the formula would be

For the Numeric Portion: =Left(C1,3)
For the Text: =MID(C1,4,5)

Then sort on those two columns

HTH

PC


"momsawake" <[email protected]> wrote in message
news:[email protected]...
> Been beating my head against sorting mixed item numbers for about 2 hours
> myself; got the alphanumeric mix converted to text but that creates a new
> problem in that 121 sorts before 12, so I'm no better off. Idea?
>
> "Bill Martin -- (Remove NOSPAM from addre" wrote:
>
> > Ray wrote:
> > > I would like to know if there is a way to do an alphanumeric sort on
some
> > > data. I have item numbers like:
> > > 100
> > > 100A
> > > 200
> > > 300XX
> > >
> > > A current sort returns
> > > 100
> > > 200
> > > 100A
> > > 300XX
> > >
> > > Any Ideas?
> >
> > Is this the sort order you're trying to get to?
> >
> > 100
> > 100A
> > 200
> > 300XX
> >
> > The sort you showed is apparently the result of your column being some
> > numbers and some text mixed in together. Make all the cells text and it
> > sorts into the order that I've shown above.
> >
> > If you have a few cells you can edit them and place a ' at the beginning
> > of the cell to force it to text.
> >
> > If you've got a bunch of them then you might create another column:
> >
> > [ ] = TEXT(A1,"########")
> >
> > This column will be text and you can either sort on that, or copy the
> > new column and PasteSpecial/Values over top of the original data to
> > force it to become text.
> >
> > Good luck....
> >
> > Bill
> >
> >