Sorting Numbers w/Aplha Suffix

[Moonray80]

Is there any way to properly sort a series of numbers with an alpha suffix?
Example:

92001
92001A
92001B
91005

I've tried to convert the numbers to text but the alpha suffix nubers still
end up in a separate group.

[Bryan Hessey]

try

="'"&A1

equals doublequote singlequote doublequote and A1

and formula copy that down your range, and sort over the new (helper) column

if your numbers are differing lengths you can use the Text(A1,"000000") on the numeric portion to give the correct view.

Is there any way to properly sort a series of numbers with an alpha suffix?
Example:

92001
92001A
92001B
91005

I've tried to convert the numbers to text but the alpha suffix nubers still
end up in a separate group.

[Melissa]

A post titled "Sorting Issue. Please help" on 14 Nov suggests several ways to
answer your question. I personally think Ken Wright's 1st suggestion is the
most straight-forward. Copied here for you:

Convert everything to text. Assuming your data is in Col A, then in Col B
use
=""&A2 and copy down. Then copy and paste special as values and delete Col
A. Now sort on your data as you wish.

Please note diff with Bryan's suggestion. Here, it's equal doubleQuote
doubleQuote ampersand A2
"Moonray80" wrote:

> Is there any way to properly sort a series of numbers with an alpha suffix?
> Example:
>
> 92001
> 92001A
> 92001B
> 91005
>
> I've tried to convert the numbers to text but the alpha suffix nubers still
> end up in a separate group.
>

[Bryan Hessey]

Melissa,

It appeared that the only way to stop the sort from determining that some cells were numbers was to put a ' single quote into the cell.
This then allows 0006 to be followed by 0006A and 0007

A post titled "Sorting Issue. Please help" on 14 Nov suggests several ways to
answer your question. I personally think Ken Wright's 1st suggestion is the
most straight-forward. Copied here for you:

Convert everything to text. Assuming your data is in Col A, then in Col B
use
=""&A2 and copy down. Then copy and paste special as values and delete Col
A. Now sort on your data as you wish.

Please note diff with Bryan's suggestion. Here, it's equal doubleQuote
doubleQuote ampersand A2
"Moonray80" wrote:

> Is there any way to properly sort a series of numbers with an alpha suffix?
> Example:
>
> 92001
> 92001A
> 92001B
> 91005
>
> I've tried to convert the numbers to text but the alpha suffix nubers still
> end up in a separate group.
>

[Melissa]

Bryan,
your suggestion requires the cells to be modified.

After using Ken's suggestion, i.e. converting to text via =""&A1 then
copying and pasting as values, I am prompted with the following message when
I try to sort the data:
The following sort key may not sort as expected because it contains some
numbers formatted as text.
What would you like to do:
- Sort anything that looks like a number, as a number
- Sort numbers and numbers stored as text separately

Option 1 will give the result that MoonRay required.

"Bryan Hessey" wrote:

>
> Melissa,
>
> It appeared that the only way to stop the sort from determining that
> some cells were numbers was to put a ' single quote into the cell.
> This then allows 0006 to be followed by 0006A and 0007
>
>
> Melissa Wrote:
> > A post titled "Sorting Issue. Please help" on 14 Nov suggests several
> > ways to
> > answer your question. I personally think Ken Wright's 1st suggestion
> > is the
> > most straight-forward. Copied here for you:
> >
> > Convert everything to text. Assuming your data is in Col A, then in
> > Col B
> > use
> > =""&A2 and copy down. Then copy and paste special as values and delete
> > Col
> > A. Now sort on your data as you wish.
> >
> > Please note diff with Bryan's suggestion. Here, it's equal doubleQuote
> > doubleQuote ampersand A2
> > "Moonray80" wrote:
> >
> > > Is there any way to properly sort a series of numbers with an alpha
> > suffix?
> > > Example:
> > >
> > > 92001
> > > 92001A
> > > 92001B
> > > 91005
> > >
> > > I've tried to convert the numbers to text but the alpha suffix nubers
> > still
> > > end up in a separate group.
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=486113
>
>

[Bryan Hessey]

Melissa,

You are correct that the ' (tick) is not required, but I couldn't achieve a correct sort the other day without it. However, it is option 2, (- Sort numbers and numbers stored as text separately) (the numbers are all stored as text) that produces the requested output, although I think the OP wanted 91005 to sort first rather than as displayed, last. Selecting option 1 (- Sort anything that looks like a number, as a number) separates the alpha-inclusive items to the end of the sort.

The other problem would be that the data shown was 5 digit numbers plus maybe an alpha. If the extended list includes 3 and 4 digit numbers these will need to be zero-filled to the size of the largest number for sorting purposes. For this reason a helper column for sorting purposes is usually recommended.

Bryan,
your suggestion requires the cells to be modified.

After using Ken's suggestion, i.e. converting to text via =""&A1 then
copying and pasting as values, I am prompted with the following message when
I try to sort the data:
The following sort key may not sort as expected because it contains some
numbers formatted as text.
What would you like to do:
- Sort anything that looks like a number, as a number
- Sort numbers and numbers stored as text separately

Option 1 will give the result that MoonRay required.

"Bryan Hessey" wrote:

>
> Melissa,
>
> It appeared that the only way to stop the sort from determining that
> some cells were numbers was to put a ' single quote into the cell.
> This then allows 0006 to be followed by 0006A and 0007
>
>
> Melissa Wrote:
> > A post titled "Sorting Issue. Please help" on 14 Nov suggests several
> > ways to
> > answer your question. I personally think Ken Wright's 1st suggestion
> > is the
> > most straight-forward. Copied here for you:
> >
> > Convert everything to text. Assuming your data is in Col A, then in
> > Col B
> > use
> > =""&A2 and copy down. Then copy and paste special as values and delete
> > Col
> > A. Now sort on your data as you wish.
> >
> > Please note diff with Bryan's suggestion. Here, it's equal doubleQuote
> > doubleQuote ampersand A2
> > "Moonray80" wrote:
> >
> > > Is there any way to properly sort a series of numbers with an alpha
> > suffix?
> > > Example:
> > >
> > > 92001
> > > 92001A
> > > 92001B
> > > 91005
> > >
> > > I've tried to convert the numbers to text but the alpha suffix nubers
> > still
> > > end up in a separate group.
> > >
>
>
> --
> Bryan Hessey
> ------------------------------------------------------------------------
> Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
> View this thread: http://www.excelforum.com/showthread...hreadid=486113
>
>