Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Sorting Numbers with letter suffixes

Is there a way I can sort numbers such that if I have a letter suffix (ie;
2A, 3B, etc), they are sorted after the original number, ie; 2A between 2
and 3 then 3B between 3 and 4?

Thanks.


  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Sorting Numbers with letter suffixes

Yes, you can sort numbers with letter suffixes in Microsoft Excel. Here's how:
  1. Select the column that contains the numbers with letter suffixes that you want to sort.
  2. Click on the Data tab in the ribbon at the top of the screen.
  3. Click on the Sort button in the Sort & Filter section of the ribbon.
  4. In the Sort dialog box, select the column that contains the numbers with letter suffixes in the Sort by dropdown menu.
  5. In the Sort On dropdown menu, select Values.
  6. In the Order dropdown menu, select Smallest to Largest.
  7. Click on the Add Level button to add a secondary sorting level.
  8. In the Sort by dropdown menu for the secondary sorting level, select the column that contains the letter suffixes.
  9. In the Order dropdown menu for the secondary sorting level, select A to Z if you want the letter suffixes to be sorted in alphabetical order.
  10. Click on the OK button to apply the sorting.

Now your numbers with letter suffixes should be sorted in the order you specified, with the letter suffixes sorted after the original numbers.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Sorting Numbers with letter suffixes

One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix (ie;
2A, 3B, etc), they are sorted after the original number, ie; 2A between 2
and 3 then 3B between 3 and 4?

Thanks.



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Sorting Numbers with letter suffixes

Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort these
randomly within the digits begining with 2s.


"Max" wrote in message
...
One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix
(ie; 2A, 3B, etc), they are sorted after the original number, ie; 2A
between 2 and 3 then 3B between 3 and 4?

Thanks.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Sorting Numbers with letter suffixes

well, the caveat was:
.. presuming data as posted is representative


Since you have now extended the scope,
hang around awhile for better solutions from others
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort
these randomly within the digits begining with 2s.





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Sorting Numbers with letter suffixes

Hi Terry,

Maybe this.

First up sort ascending on your data in col A.
This will leave your numbers sorted in the top of the
column and your text sorted at the bottom of the column

Copy the numbers across to column B. Then with the text
values, for this example let's say that the first text entry is in A9.
Put this in B9 and drag down to the end of your data.
=LEFT(A9,1)*1
You may need something different depending
on what your actual data is. What you want is to extract
the numbers without the letters, and to convert the
text numbers to real numbers (that's the *1 bit)

Then in col. C we pinch a bit of Max's formula.
Put this in C1 and drag down to the end of your data
=B1+ROW()/10^10

Now select all three columns A,B and C
and sort on col. C ascending.
Hopefully col A should be sorted the way you want.

HTH
Martin


"Terry Bennett" wrote in message
...
Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort
these randomly within the digits begining with 2s.


"Max" wrote in message
...
One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix
(ie; 2A, 3B, etc), they are sorted after the original number, ie; 2A
between 2 and 3 then 3B between 3 and 4?

Thanks.







  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Sorting Numbers with letter suffixes

Thanks for the suggestion Martin but it still sorts numbers like 12B ahead
of single digits like 2 and 3.

Not a problem - I can get around it manually.

I do appreciate your suggestion - many thanks.


"MartinW" wrote in message
...
Hi Terry,

Maybe this.

First up sort ascending on your data in col A.
This will leave your numbers sorted in the top of the
column and your text sorted at the bottom of the column

Copy the numbers across to column B. Then with the text
values, for this example let's say that the first text entry is in A9.
Put this in B9 and drag down to the end of your data.
=LEFT(A9,1)*1
You may need something different depending
on what your actual data is. What you want is to extract
the numbers without the letters, and to convert the
text numbers to real numbers (that's the *1 bit)

Then in col. C we pinch a bit of Max's formula.
Put this in C1 and drag down to the end of your data
=B1+ROW()/10^10

Now select all three columns A,B and C
and sort on col. C ascending.
Hopefully col A should be sorted the way you want.

HTH
Martin


"Terry Bennett" wrote in message
...
Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort
these randomly within the digits begining with 2s.


"Max" wrote in message
...
One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix
(ie; 2A, 3B, etc), they are sorted after the original number, ie; 2A
between 2 and 3 then 3B between 3 and 4?

Thanks.









  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default Sorting Numbers with letter suffixes

Hello,

If I understand the problem correctly Max was almost the
=IF(A1="","",IF(ISNUMBER(A1),A1,--LEFT(A1)+CODE(LEFT(A1))/10^10))

If there can be more than one letter suffix I suggest to use
RegExpReplace to separate numerical and text parts...

Regards,
Bernd
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 168
Default Sorting Numbers with letter suffixes

Well no it doesn't Terry,

If you change the extraction formula to this,
=LEFT(A9,LEN(A9)-1)*1

It will sort this column
1
3B
7C
12B
2
5
7A
6C
7B
12A
4
5A
4B
3A
6B
7
3C
124A
7D
124B

Like this
1
2
3A
3B
3C
4
4B
5
5A
6B
6C
7
7A
7B
7C
7D
12A
12B
124A
124B

Is that not what you are trying to do?

Regards
Martin

"Terry Bennett" wrote in message
...
Thanks for the suggestion Martin but it still sorts numbers like 12B ahead
of single digits like 2 and 3.

Not a problem - I can get around it manually.

I do appreciate your suggestion - many thanks.


"MartinW" wrote in message
...
Hi Terry,

Maybe this.

First up sort ascending on your data in col A.
This will leave your numbers sorted in the top of the
column and your text sorted at the bottom of the column

Copy the numbers across to column B. Then with the text
values, for this example let's say that the first text entry is in A9.
Put this in B9 and drag down to the end of your data.
=LEFT(A9,1)*1
You may need something different depending
on what your actual data is. What you want is to extract
the numbers without the letters, and to convert the
text numbers to real numbers (that's the *1 bit)

Then in col. C we pinch a bit of Max's formula.
Put this in C1 and drag down to the end of your data
=B1+ROW()/10^10

Now select all three columns A,B and C
and sort on col. C ascending.
Hopefully col A should be sorted the way you want.

HTH
Martin


"Terry Bennett" wrote in message
...
Thanks Max.

I see your logic but this sorts all digits begining with a '1' first ...
hence 112 comes before 2. In the case of 2, 2A, etc it seems to sort
these randomly within the digits begining with 2s.


"Max" wrote in message
...
One way which might suffice, presuming data as posted is representative

Assuming data in A1 down
Put in B1, copy down:
=IF(A1="","",IF(ISNUMBER(A1),A1,LEFT(A1)+CODE(LEFT (A1))/10^10))
Then select both cols A & B, sort by col B, ascending
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix
(ie; 2A, 3B, etc), they are sorted after the original number, ie; 2A
between 2 and 3 then 3B between 3 and 4?

Thanks.











  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 67
Default Sorting Numbers with letter suffixes

Thanks again guys for the suggestions - much appreciated.

Terry

"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix (ie;
2A, 3B, etc), they are sorted after the original number, ie; 2A between 2
and 3 then 3B between 3 and 4?

Thanks.





  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Sorting Numbers with letter suffixes

On Wednesday, February 27, 2008 at 4:42:44 AM UTC-5, Terry Bennett wrote:
Thanks again guys for the suggestions - much appreciated.

Terry

"Terry Bennett" wrote in message
...
Is there a way I can sort numbers such that if I have a letter suffix (ie;
2A, 3B, etc), they are sorted after the original number, ie; 2A between 2
and 3 then 3B between 3 and 4?

Thanks.


Change the data type to Plain text instead of Auto. Then when you sort it does it correctly.
  #12   Report Post  
Junior Member
 
Posts: 1
Default

Tool to change the format of exel file or another file
https://www.coolutils.com/TotalOutlookConverterPro
https://www.coolutils.com/TotalMailConverterPro
now im trying to convert exel to html
  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Sorting Numbers with letter suffixes

Thank God for providing the actual solution...

Everyone above is annoying.
  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Sorting Numbers with letter suffixes

On Monday, February 25, 2008 at 4:09:52 PM UTC-6, Terry Bennett wrote:
Is there a way I can sort numbers such that if I have a letter suffix (ie;
2A, 3B, etc), they are sorted after the original number, ie; 2A between 2
and 3 then 3B between 3 and 4?

Thanks.

I am in need of help exactly like this, but none of the answers so far are working for me. I have racecar #'s that I need to sort by, example 1, 1A, 2, 3, 4, 4V, 7, 8, 9, 9D, 10, 20, 30, 40, 40H, 42, 45, 45b, 60, 70, 80, 123, 132, 132A, 132C, etc. Is this possible in Google Sheets? Or is my only option to do it manually??

Stacy
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
Sorting a column from the last letter or 2 letters of a part numbe Brian Denny Excel Discussion (Misc queries) 4 November 19th 06 05:25 PM
Extracting names and suffixes Rookie_User Excel Discussion (Misc queries) 2 September 18th 06 05:53 PM
How can you get Excel to sort numbers with alpha suffixes? 1234A boneified5 Excel Discussion (Misc queries) 1 May 17th 06 04:02 PM
How do I use the numbers on the letter keys? auenmom Excel Discussion (Misc queries) 2 March 3rd 06 06:37 AM
Equations with numbers and letter values Corey Excel Discussion (Misc queries) 1 February 16th 05 03:44 AM


All times are GMT +1. The time now is 07:25 PM.

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"