sorting by number/letter

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
I have not been able to figure out or find a solution to sorting the data that we have into an order that we can work with.

Some numbers have no other associated data and other numbers have an associated a,b or c. We want to sort the data so it reads 1, 1a,1b, 1c.... 6, 7, 8, ... 58, 58a, 58b, ... for example.

I've tried formulas found on various websites and none do the job. The two (2) thumbnails show the column we want to sort by. If you can help or have suggestions that would be great.

Thank you for your time.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
If you do a straight alphabetic sort you get issues like "10"<"2",
You could put leading 0s on everything with =LEFT("000000"&A1, 6) and sort that
 

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
If you do a straight alphabetic sort you get issues like "10"<"2",
You could put leading 0s on everything with =LEFT("000000"&A1, 6) and sort that


I'm not well versed in Excel so I think I'll need more clarification. I copied and pasted =LEFT("000000"&A1, 6) into a new column "B" the values were all zeros, six (6) of them. I inserted the formula into all the cells in column B. So I'm not doing something correctly.
 

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

When using =RIGHT("0000000"&A1, 6) the data with a 1a, 1b, 1c sorted under 19; 2a, 2b, 2c sorted under 20 etc. Is there a way for 1a, 1b. 1c to sort under 1 etc? Included is a thumbnail of the current result.
 

Attachments

  • RCKY MTN VRS version 2.PNG
    RCKY MTN VRS version 2.PNG
    14.8 KB · Views: 4

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
I forgot about the trailing characters
try =RIGHT("0000000"&A1, 6)&IF(ISNUMBER(A1)," ","")
 

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Thank you for the formula, however it didn't sort in sequence. Another person I've been chatting with came up with a formula. column Q is =CONCATENATE(U1,T1) column S is =T(A1) column T is =IF(S1="","",RIGHT(S1,1)) column U is =IF(S1="",LEFT(A1,4),LEFT(S1,(LEN(S1)-1))) column V =_xlfn.NUMBERVALUE(U1) sorted by column V then T. I hope that makes since.

I appreciate your time and help on this inquiry. Have a good evening.



 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Here is another option that I think does what you want with just a single helper column. You could reduce the number of leading zeros if your initial numbers are short. I have allowed for leading numbers up to 10 digits.

20 09 01.xlsm
AB
1DataHelper
27|0000000007
358|0000000058
46|0000000006
558c|0000000058c
61b|0000000001b
758a|0000000058a
81c|0000000001c
958b|0000000058b
Custom sort
Cell Formulas
RangeFormula
B2:B9B2=TEXT(LEFT(A2,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)),"|0000000000")&REPLACE(A2,1,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),"")



Now sort on 'Helper' column. The Helper column can be hidden if you want.
Result of sort:

20 09 01.xlsm
AB
1DataHelper
21b|0000000001b
31c|0000000001c
46|0000000006
57|0000000007
658|0000000058
758a|0000000058a
858b|0000000058b
958c|0000000058c
Custom sort
Cell Formulas
RangeFormula
B2:B9B2=TEXT(LEFT(A2,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)),"|0000000000")&REPLACE(A2,1,AGGREGATE(14,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1),"")
 

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
I will give this a try as well and give them both to my partner and see how he likes the two. Thank you for the simpler version. Our data sets run from the hundreds to the 10's of thousands. Thank you for showing me this formula as an additional option. Have a good day!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,560
Office Version
  1. 365
Platform
  1. Windows
Our data sets run ... to the 10's of thousands.
Hmm, I'll be interested to see how such a formula approach goes for you in terms of speed, especially when you do the sort.
If it is too slow, there may be other options to investigate.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,129
Messages
5,640,296
Members
417,135
Latest member
zeusmining

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top