Sort

[Vanda_a]

Hi all

Please help me with this Sort function

	A
1	A3
2	A300
3	A4
4	A400

<tbody>
</tbody>

Why I cant sort it to this?

	A
1	A3
2	A4
3	A300
4	A400

<tbody>
</tbody>

Thank you

 

[Misca]

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)))

 

[WarPigl3t]

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

 

[Vanda_a]

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?

 

[Vanda_a]

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?

 

[WarPigl3t]

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.

 

[Vanda_a]

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

 

[Peter_SSs]

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.

 

[azumi]

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

 

[Vanda_a]

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