Sorting numbers that end in letters

[Jenifred]

I'm trying to sort a column that lists IDs that include letters and numbers. The issue is that some IDs are just numbers (450), while others end in letters (450A, 450B, etc.) I want it to sort as 450, 450A, 450B, 451, 452A, etc., but instead its sorting as 450, 451, 450A, 450B." I've tried telling it to sort as text, general, and as numbers. Any quick and easy solutions? The spreadsheet is small enough that I can live with it if the solution starts getting complicated.

 

[xlhelp15]

Honestly - I found this in my personal macro-book document which i saved and used few years ago. Hope it helps !
Through UDF with the help of VBA Macro you can achieve it,

copy the below code in your Module,

Function Num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
Num = Num & Mid(rng, n, 1)
End If
Next n
End Function

and go back to your sheet, type =num(input your cell) then press enter and drag till end of the row. Post using the sort method, you can sort it through ALT+D+S.

Value	Sort
450	450
450A	450
451	451
451A	451

 

[DanteAmor]

Hi and welcome to MrExcel!

In an auxiliary column put the following formula. Then sort the data by the auxiliary column and then by the number column.

Dante Amor
	A	B	C
1	A	NUMS	AUX
2	2	450	450
3	4	450A	450
4	5	450B	450
5	3	451	451
6	6	452A	452
Hoja2

Cell Formulas
Range	Formula
C2:C6	C2	=IF(ISNUMBER(SEARCH(RIGHT(B2,1),"0123456789")),B2,LEFT(B2,LEN(B2)-1))+0

 

[Jenifred]

Honestly - I found this in my personal macro-book document which i saved and used few years ago. Hope it helps !
Through UDF with the help of VBA Macro you can achieve it,

copy the below code in your Module,

Function Num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
Num = Num & Mid(rng, n, 1)
End If
Next n
End Function

and go back to your sheet, type =num(input your cell) then press enter and drag till end of the row. Post using the sort method, you can sort it through ALT+D+S.

Value	Sort
450	450
450A	450
451	451
451A	451

Honestly - I found this in my personal macro-book document which i saved and used few years ago. Hope it helps !
Through UDF with the help of VBA Macro you can achieve it,

copy the below code in your Module,

Function Num(rng As Range) As String
Dim n As Integer
For n = 1 To Len(rng)
If Mid(rng, n, 1) Like "[0-9]" Then
Num = Num & Mid(rng, n, 1)
End If
Next n
End Function

and go back to your sheet, type =num(input your cell) then press enter and drag till end of the row. Post using the sort method, you can sort it through ALT+D+S.

Value	Sort
450	450
450A	450
451	451
451A	451

Thank you so much for the quick reply, but unfortunately this is way above my excel capabilities. But I may forward it to my IT department and ask them to implement it.

 

[Jenifred]

Thanks for the quick reply. This seems much more suited to my capabilities, but I'm still having problems. My numbers are in column A, so where you have B2, should I change that to A2? And should I change where you have "ISNUMBER" to column heading that I actually use?

Hi and welcome to MrExcel!

In an auxiliary column put the following formula. Then sort the data by the auxiliary column and then by the number column.

Dante Amor
	A	B	C
1	A	NUMS	AUX
2	2	450	450
3	4	450A	450
4	5	450B	450
5	3	451	451
6	6	452A	452
Hoja2

Cell Formulas
Range	Formula
C2:C6	C2	=IF(ISNUMBER(SEARCH(RIGHT(B2,1),"0123456789")),B2,LEFT(B2,LEN(B2)-1))+0

 

[DanteAmor]

My numbers are in column A, so where you have B2, should I change that to A2?

That's right, it should be like this:

Dante Amor
	A	B
1	NUMS	AUX
2	450	450
3	450A	450
4	450B	450
5	451	451
6	452A	452
Hoja2

Cell Formulas
Range	Formula
B2:B6	B2	=IF(ISNUMBER(SEARCH(RIGHT(A2,1),"0123456789")),A2,LEFT(A2,LEN(A2)-1))+0

And should I change where you have "ISNUMBER" to column heading that I actually use?

No, you should not remove or change the IsNumber function, the IsNumber function is used in the formula to check if the last character of the "numbers" is a letter or is a number.