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.
Melissa Wrote:
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
------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=486113