Sorting/applying forumal to same column

[Michael1727]

Hello. at first I'm trying to sort a column of numbers numerically which contain numbers of different digit lengths. The sorting tool is sorting only the total cell value and is sorting numbers that are longer in digit value as larger than numbers of greater numerical value with a shorter length. Here's an example of how the data is currently being sorted:
Column D
210251058001
22307107007
22089160007
2300194253
2300194246

But I would like for the sorting to be numerically from largest to smallest to look like this:
Column D
2300194253
2300194246
22307107007
22089160007
210251058001

I have not had any success with any solutions to format the data so that the formatting tool will sort the data numerically, and so I saw a solution that I could make all the data 12-14 digits long with trailing zeroes. I have over 50,000 rows of data so I wanted to find a way to format all the data to have trailing zeroes in the single column. I saw an idea that a formula could be applied to the entire column to multiple all the data by a number to make them all, for example, 14 digits and then the cell value would be the same. But I'm not sure how to apply a multiplication formula to an entire column or whether this will produce the outcome I'm looking for.

I hope I've explained the situation properly. If not I will try to clarify.

 

[anariskenderli]

Hello. Maybe it is not the best way, but I solve this kind of problem by this way:

First, insert a new column and format it as text.
Second, copy all the 50000 numbers and past them to notepad.
Third, copy all the numbers from the notepad and past them to text formatted new column.
Forth, custom sort the new column and select "Sort numbers and numbers stored as text separately".

It will solve your problem.

 

[GuzmanServices]

Assuming your Data starts on Cell A1, I would recommend using a couple of helper columns.

- Starting on Cell B1, use the following formula...
=IF(LEN(A1)<11,A1*100,IF(LEN(A1)<12,A1*10,A1))
...then standardize the number of digits, by changing the format to "000000000000"

- Starting on Cell C1, use the following formula...
=RANK(B1,$B$1:$B$50000)
...assuming your Data set ends on Row 50000.

-Starting on Cell D1, use the following formula...
=INDEX(B:B,MATCH(ROW(C1),C:C,0))

 

[kevin9999]

When you select your range, from the data tab select Sort.
Sort by Header
Sort On Cell Values
Order A to Z
Click OK

You should get a message box that gives you the option to ""Sort anything that looks like a number, as a number"
Click OK

Your text formatted numbers should be sorted as numbers in the order you indicate in your original post:
Before:

Book1
	D
1	Header
2	210251058001
3	22307107007
4	22089160007
5	2300194253
6	2300194246
Sheet2

After:

Book1
	D
1	Header
2	2300194246
3	2300194253
4	22089160007
5	22307107007
6	210251058001
Sheet2

 

[fjns]

Hi, see the linked file for a possible solution...

SortAnalysis.xlsx

 

[awoohaw]

What is the reasoning behind having numeric values as text? I realize there are valid reasons for it (account numbers is one example).
But unless there is a valid reason, treating text numbers of incongruent length is always a difficulty, Lookup tables can be difficult to manage if you don't want exact matches, for instance.

You do have Kevin's solution in Post #4 of "Sort Text as Numbers" above.

 

[awoohaw]

Here a couple examples of how lookups can be really strange with numbers stored as text:

Book1
	D	E	F	G	H	I
1	Column D			column D converted to numbers and pasted as values, then sorted. And left aligned		Column D sorted where if it looks like a number sort it like a number.
2	210251058001			2300194246		2300194246
3	22307107007			2300194253		2300194253
4	22089160007			22089160007		22089160007
5	2300194253			22307107007		22307107007
6	2300194246			210251058001		210251058001
7
8				21025105800		21025105800
9				2		#N/A
10				=MATCH(G8,$G$2:$G$6)		=MATCH(I8,$I$2:$I$6)
Sheet3

Cell Formulas
Range	Formula
G9	G9	=MATCH(G8,$G$2:$G$6)
G10,I10	G10	=FORMULATEXT(G9)
I9	I9	=MATCH(I8,$I$2:$I$6)

 

[fjns]

I see that the solution is not in #4...

 

[awoohaw]

I see that the solution is not in #4...

View attachment 90658

Good pick up @fjns , I'm curious as to how the OP wants this sorted numerically then. Maybe the OP had a typo in the desired result..
@Michael1727 , please advise.

 

[kevin9999]

Yes, well spotted @fjns (@Alex Blakenburg had already pointed this out to me via DM) so in the event that this wasn't merely a typo, I'll offer the following VBA option. I only considered column D (we may not be seeing the entire sheet and the code might need to be adjusted) but it seems to meet the requirements.

Sub Michael1727()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")   '<~~ Change to actual sheet name
    
    Dim LRow As Long, LCol As Long, r As Range
    LRow = ws.Cells(Rows.Count, "D").End(xlUp).Row
    LCol = Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    Set r = ws.Range("D1:D" & LRow)
    Dim a, b, n As Long, i As Long
    a = r
    n = Evaluate("Max(len(" & r.Address & "))")
    ReDim b(1 To UBound(a, 1), 1 To 1)
    
    For i = LBound(a, 1) To UBound(a, 1)
        b(i, 1) = a(i, 1) & WorksheetFunction.Rept(0, n - Len(a(i, 1)))
    Next i
    ws.Cells(1, LCol).Resize(UBound(b, 1)).Value = b
    
    ws.Range(ws.Cells(1, 4), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(1, LCol), _
    order1:=xlDescending, Header:=xlNo
    ws.Columns(LCol).ClearContents
    Application.ScreenUpdating = True
End Sub

Before:

michael1727.xlsm
	D
1	210251058001
2	22089160007
3	22307107007
4	2300194246
5	2300194253
Sheet1

After:

michael1727.xlsm
	D
1	2300194253
2	2300194246
3	22307107007
4	22089160007
5	210251058001
Sheet1