Question

Locked

Excel 2007 Sorting

By deadlypumpkin ·
Hello,

I have imported a document into excel as text an trying to sort a column. For example, I have aa1,aa6,aa10,aa25,aa2,aa11. I would like them to be sorted as aa1,aa2,aa6,aa10,aa11,aa25, but it keeps sorting like aa1,aa10,aa11,aa2,aa25,aa6. How do I get it to sort the way I would like. I'm new to VBE and SQL so please bear with me. Gotten some tips on codes but don't understand how to write them in.

Thanks

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Microsoft Excel 2007 How to Sort Data.....

by Peconet Tietokoneet In reply to Excel 2007 Sorting

Step by step here:
http://www.homeandlearn.co.uk/excel2007/excel2007s3p1.html

Collapse -

Strings vs Numerical

by Kingbackwards In reply to Excel 2007 Sorting

What your dealing with is that excel is sorting strings. So aa10 is less than aa6 as a string even though numerically 10 is greater than 6. Because in the third character place 1 is less than 6.

There are two ways to get around your issue. First is add leading zeros so that you have aa01, aa02, aa06, aa10, etc. Its usually easier to do that is before it gets to excel.

Or you need remove the "aa" and convert the remaining text to a numerical value.

Heres a quick formula to cut the leading 2 characters off and change the value to an integer.

=VALUE(RIGHT(A1; LEN(A1)-2))

Then sort by the number column but include the source column in your selection when sorting.

This can get a bit more complicated if you have a bb or cc present and would need to generate some IF statements to make the aa be a 100's and the bb 200's and so on.

Collapse -

RE: String Vs Numerical

by deadlypumpkin In reply to Strings vs Numerical

Thanks practicing it now and it works. Thanks again

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums