Sort

Vanda_a

Well-known Member
Joined
Oct 29, 2012
Messages
939
Hi all

Please help me with this Sort function

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A300
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A400
[/TD]
[/TR]
</tbody>[/TABLE]

Why I cant sort it to this?

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]A3
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]A4
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]A300
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]A400
[/TD]
[/TR]
</tbody>[/TABLE]
Thank you
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
It is text, not numbers you're trying to sort. Text has no values so it's sorted by the characters: 3 comes before 4, no matter what it is followed by.

If you want to sort that by their numeric values you'd need to use a helper column or VBA.

You'll get the numeric values of the cells with something like =VALUE(MID(A1,2,LEN(A1)))
 
Last edited:
Upvote 0
Agreed. And to be more clear about what Misca is talking about... Make a new column. Lets say your new column is column B. In cell B1, have it equal =RIGHT(A1,LEN(A1)-1)
Then you can sort using that helper column B
 
Upvote 0
It is text, not numbers you're trying to sort. Text has no values so it's sorted by the characters: 3 comes before 4, no matter what it is followed by.

If you want to sort that by their numeric values you'd need to use a helper column or VBA.

A helper column. What is that? Please advise how to get it?
 
Upvote 0
Agreed. And to be more clear about what Misca is talking about... Make a new column. Lets say your new column is column B. In cell B1, have it equal =RIGHT(A1,LEN(A1)-1)
Then you can sort using that helper column B

Thank for your help. Any other way beside formula nor VBA?
 
Upvote 0
I just explained what a helper column was in my last post. To be more clear, a helper column is a column that has no use to the user, but it is used to accomplish a task of automation. So when I told you to create a new column B to input that formula, it has no use to you as the user. But it is used to automate your sort feature.
 
Upvote 0
I just explained what a helper column was in my last post. To be more clear, a helper column is a column that has no use to the user, but it is used to accomplish a task of automation. So when I told you to create a new column B to input that formula, it has no use to you as the user. But it is used to automate your sort feature.

I understand now. so there is only this way?

Thank you so much
 
Upvote 0
I don't believe you will find a way to do that sort without either VBA or Formula. (Edit: Depending on the answers below, there may be a way)


Does the data always have an "A" before the number?

Does the the data always have a single letter before the number?

If either answer is "No", please give a more representative data sample and the expected results.
 
Last edited:
Upvote 0
Just only for your example data, assumed your data in Column A and ranges A2:A5:

Im perfecting formula from Misca:

=LEFT(A2,1)&SMALL(VALUE(MID($A$2:$A$5,2,LEN($A$2:$A$5))),ROWS($A$1:A1))

Its Array Formula, you need to confirm press CTRL-SHIFT-ENTER button together and copied down
 
Upvote 0
I don't believe you will find a way to do that sort without either VBA or Formula. (Edit: Depending on the answers below, there may be a way)


Does the data always have an "A" before the number?

Does the the data always have a single letter before the number?

If either answer is "No", please give a more representative data sample and the expected results.

There is not always A before number but there will be only one letter.

Thank you
 
Upvote 0

Forum statistics

Threads
1,226,619
Messages
6,192,055
Members
453,693
Latest member
maverick688

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top