Sort on first 10 digits

gambils

Board Regular
Joined
Apr 22, 2009
Messages
244
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!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,680
Office Version
365
Platform
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

Board Regular
Joined
Apr 22, 2009
Messages
244
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,680
Office Version
365
Platform
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

Board Regular
Joined
Apr 22, 2009
Messages
244
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!
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,662
Messages
5,512,680
Members
408,909
Latest member
Burnrose

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top