Sort on first 10 digits

[gambils]

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!

 

[JackDanIce]

In a new column next to your data, enter the formula:

=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:

=IF(LEN(A1)>10,LEFT(A1,10)+0.1,A1+0)

And then again drag down and sort based on this column

 

[gambils]

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]

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]

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!