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.
 

Zelligar

New Member
Joined
Aug 25, 2020
Messages
6
Office Version
  1. 2010
Platform
  1. Windows
At the moment I'm doing small data sets from 100 to 200 points. I haven't had an opportunity to work with any data sets today, hoping for tomorrow morning.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,544
Office Version
  1. 365
Platform
  1. Windows
If anything more is required it would be good to know ..
  • How big the number parts might actually be.
  • Whether any alpha part is only ever one lower case letter. If not some more information about what variety can occur would help.
 

RAJESH1960

Active Member
Joined
Mar 26, 2020
Messages
478
Office Version
  1. 2019
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),"")
x
 

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,108
Members
417,072
Latest member
JaimeDee

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