Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Moonray80
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
Bryan Hessey
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix


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.


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

  #3   Report Post  
Posted to microsoft.public.excel.misc
Melissa
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix

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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
Bryan Hessey
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix


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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Melissa
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix

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




  #6   Report Post  
Posted to microsoft.public.excel.misc
Bryan Hessey
 
Posts: n/a
Default Sorting Numbers w/Aplha Suffix


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
Sorting numbers JTH New Users to Excel 4 September 23rd 05 05:45 PM
Sorting Numbers with Multiple Decimals (cont.) Intern Ian Excel Discussion (Misc queries) 5 September 21st 05 12:04 AM
Match Last Occurrence of two numbers and Return Date Sam via OfficeKB.com Excel Worksheet Functions 6 April 5th 05 12:40 PM
Match Last Occurrence of two numbers and Count to Previous Occurence Sam via OfficeKB.com Excel Worksheet Functions 33 April 4th 05 02:17 PM


All times are GMT +1. The time now is 03:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"