Re: Sorting Numbers w/Aplha Suffix
- From: Bryan Hessey <Bryan.Hessey.1yu4yo_1132557031.58@xxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 21 Nov 2005 01:04:03 -0600
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?action=getinfo&userid=21059
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=486113
> >
> >
--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=21059
View this thread: http://www.excelforum.com/showthread.php?threadid=486113
.
- References:
- Re: Sorting Numbers w/Aplha Suffix
- From: Bryan Hessey
- Re: Sorting Numbers w/Aplha Suffix
- From: Melissa
- Re: Sorting Numbers w/Aplha Suffix
- Prev by Date: Re: Can I access the previous version of a saved file?
- Next by Date: IF Statement
- Previous by thread: Re: Sorting Numbers w/Aplha Suffix
- Next by thread: Re: Excel Capital Letters
- Index(es):
Relevant Pages
|
