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"

 

[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

 

[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

 

[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]

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.