CODEWITHSUNDEEP
excel
Samantha Pullman
Samantha Pullman

Reputation: 3

Excel is sorting a 3 digit combination numer before a 2 digit

The following is a list of mine I am trying to sort out. The problem is that Excel is sorting it based on what I assume is the asce characters, therefore 130 is "less" than 14. Is there a VBA function to fix this? I have attempted to change the formatting to text, numbers, decimals, etc. but nothing seems to work. and it is case sensitive so I cannot input a 0 before the 14, as much as I would like to!

This is how excel sorts the numbers:

This is what I am looking for:

EDIT::: After attempting to use the helper column, this is now what the data looks like

Using the Helper column that looks like this:

using this formula =RIGHT(B2,(LEN(B2))-FIND("-",B2,1)*1)

As you can tell the C comes before A, because now it is sorting it exclusively by the number. The page referenced in the comments helped a great deal, but those had a constant variable they were referring to where as mine can be any alphabetical letter following the numerical.

Upvotes: 0

Views: 2580

Answers (2)

FreeMan
FreeMan

Reputation: 5687

What Dubison posted, but slightly different. His presumes that the starting digit will always be 2, while this will work no matter what the starting digit is (including a multi-digit value like 37):

The data and the sort setup: enter image description here

The sort dialog box:
enter image description here

Upvotes: 0

Dubison
Dubison

Reputation: 768

I managed to desired result with following formulas:

My worksheet layout is like this:

enter image description here

and formulas:

B1: =MID(A1,2,1) (Just to get the letter, you can retrieve it with several methods)

C1: =IFERROR(REPLACE(RIGHT(A1,LEN(A1)-FIND("-",A1,1)),FIND(".",RIGHT(A1,LEN(A1)-FIND("-",A1,1)),1),1,","),RIGHT(A1,LEN(A1)-FIND("-",A1,1))) (To get the numbers and convert "." to "," which is my decimal separator)

then I applied "Custom Sort" under "Sort and Filter" as follows:

enter image description here

and if you get any warning:

enter image description here

if everything goes well you should get the result like:

enter image description here

Upvotes: 1

Related Questions