# Sort on first 10 digits

#### gambils

##### Active Member
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

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?

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

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!

Replies
1
Views
269
Replies
7
Views
255
Replies
0
Views
237
Replies
1
Views
188
Replies
8
Views
880

Threads
1,214,779
Messages
6,121,516
Members
449,036
Latest member
dudeinaghillie

### 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

### 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