Sort on first 10 digits

gambils

Active Member
Joined
Apr 22, 2009
Messages
253
I have a list that I want to sort on the first 10 digits- a cost center number- but some of them have additional characters at the end- CS- to be exact. So I want 4046750000 and 404675000CS to appear one after the other but instead the ones with the alpha ending get sorted to the bottom.

Figure there must be an easy fix- could someone clue me in?

This would actually be the 2nd level of the sort with the first level being a last name.

Thanks!
 

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.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
In a new column next to your data, enter the formula:
Code:
=LEFT(A1,10)
Copy and drag down to the last row of your data and then sort all your data based on this code, small to large. This column should only contain the numberic portion of the cost-centre numbers (i.e. ignoring the alphanumeric characters) and thus you'll have your data sorted as you want?

Or alternatively use the formula:
Code:
=IF(LEN(A1)>10,LEFT(A1,10)+0.1,A1+0)
And then again drag down and sort based on this column
 

gambils

Active Member
Joined
Apr 22, 2009
Messages
253
Ok, so I have to insert another column...

The first formula is the easiest for me to grasp :) so I'll use it, but curious on the 2nd- what does LEN stand for?
 

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,900
Office Version
  1. 365
Platform
  1. Windows
I don't think it'll be too much effort to insert another column is it? ;)

LEN(A1) returns the number of characters in a cell
e.g. If cell A1 has the value: 1234567890AB
LEN(A1) = 12

Since you're evaluating cost-centre numbers of length 10, this should conform to this
e.g if you've got two CS's:
1234567890AB
1234567890

Then the formula will change them to:
1234567890.1
1234567890

Which will then be sorted to (if you're using ascending order)
1234567890
1234567890.1

Hope this helps
 

gambils

Active Member
Joined
Apr 22, 2009
Messages
253
No, not too hard to insert the column- just that the old man's progressive lenses already make it hard for me to see. So every time I add a column becomes tougher to see on the current screen without scrolling- I need to move from a 19" monitor to a 37" HD! :)

And, yes, suppose I could hide the column I sort on but then I forget it's there- the memory has gone with the eyes! :)

Thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,441
Members
431,879
Latest member
KiwDaWabbit

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