| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by number not by letter (C1, A2, B3, D4)
I have numbers with a letter in front. I want to sort them by the number and ignore the letter. The only way Excel does it is by the letter (unless I'm missing something). Here is an example of my list: A541 A586 A600 A618 A660 B299 B327 B416 B421 B501 B547 B567 L917 M125 N207 P196 PI93 T279 T284 T312 V180 V324 X327 X341 Z315 Z318 Z340 Z372 As you can see, it sorts it by the letter first, then number. I want it to sort by the number first, then the letter. Does anyone know how I can do this? Thanks! -- dukemeiser ------------------------------------------------------------------------ dukemeiser's Profile: http://www.excelforum.com/member.php...o&userid=34141 View this thread: http://www.excelforum.com/showthread...hreadid=539114 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort by number not by letter (C1, A2, B3, D4)
One of your entries has two leading alphas.
That complicates things. With only one leading alpha, you can enter the following formula in an adjoining column, fill down and then sort both columns using the new column as the sort column. Then clear the new column... =RIGHT(B5,LEN(B5)-1) -- Jim Cone San Francisco, USA http://www.officeletter.com/blink/specialsort.html "dukemeiser" wrote in message I have numbers with a letter in front. I want to sort them by the number and ignore the letter. The only way Excel does it is by the letter (unless I'm missing something). Here is an example of my list: A541 A586 A600 A618 A660 B299 B327 B416 B421 B501 B547 B567 L917 M125 N207 P196 PI93 T279 T284 T312 V180 V324 X327 X341 Z315 Z318 Z340 Z372 As you can see, it sorts it by the letter first, then number. I want it to sort by the number first, then the letter. Does anyone know how I can do this? Thanks! -- dukemeiser |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort by number not by letter (C1, A2, B3, D4)
Hey, it worked! Thanks a million! -- dukemeiser ------------------------------------------------------------------------ dukemeiser's Profile: http://www.excelforum.com/member.php...o&userid=34141 View this thread: http://www.excelforum.com/showthread...hreadid=539114 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sort by number not by letter (C1, A2, B3, D4)
I'm glad as the formula to handle 1 or 2 leading characters is six times longer.
Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "dukemeiser" wrote in message Hey, it worked! Thanks a million! -- dukemeiser |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I sort multiple times | Excel Worksheet Functions | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Converting a number to a letter in Excel | Excel Worksheet Functions | |||
| How do I sort alphabetically by last letter in Excel? | Excel Discussion (Misc queries) | |||
| Get Column Letter from Column Number | Excel Worksheet Functions | |||
Hybrid Mode
