Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Seed numbers for random number generation, uniform distribution | Excel Discussion (Misc queries) | |||
Sorting numbers | New Users to Excel | |||
Sorting Numbers with Multiple Decimals (cont.) | Excel Discussion (Misc queries) | |||
Match Last Occurrence of two numbers and Return Date | Excel Worksheet Functions | |||
Match Last Occurrence of two numbers and Count to Previous Occurence | Excel Worksheet Functions |