# Software

## Question

Locked

### Excel 2007 Sorting

By ·
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.

Thread display: Collapse - | Expand +

Collapse -

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

by In reply to Excel 2007 Sorting
Collapse -

### Strings vs Numerical

by 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 In reply to Strings vs Numerical

Thanks practicing it now and it works. Thanks again

## Related Discussions

• 1

zlzpqx ·

• 1

ITguy1986 ·

• 1

#### by which software I can make my PD portable?

webtechexprt ·

• 8

#### what is the best chatbot software for small business?

nathanlohey ·

• 2

Buchiiii ·