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.

 

[Michael1727]

I've tried the "format as text and sort everything as text or text and number separately" and I receive the same result where it is just sorted largest to smallest by cell value.

 

[kevin9999]

I've tried the "format as text and sort everything as text or text and number separately" and I receive the same result where it is just sorted largest to smallest by cell value.

I'd be interested in finding out how you go with the VBA in post #10.

 

[awoohaw]

It seems you need to do a lot of cell debugging:

As some tests:
Change your data column format to "General"
use helper columns,
in one column multiply the value in you column by 1. cellref*1
in another, get the cell length of each text value len(cellref).
In another column get the first character:; Left(cellref, 1)
In another column get the last character: right(cellref, 1)

If all make sense then you should have numbers with no goofy non printing characters, etc.

 

[Alex Blakenburg]

I've tried the "format as text and sort everything as text or text and number separately" and I receive the same result where it is just sorted largest to smallest by cell value.

Try this:
1) Make sure that all the values are text.
One way to do this is Select the column
then Data > Text to Columns > Delimited (which one won't matter) > Click the Text option > Finish

2) Sort
Make sure you select the option to sort numbers stored as text separately.

 

[Michael1727]

Try this:
1) Make sure that all the values are text.
One way to do this is Select the column
then Data > Text to Columns > Delimited (which one won't matter) > Click the Text option > Finish

2) Sort
Make sure you select the option to sort numbers stored as text separately.

View attachment 90689

Hey, this worked! I had played around with formatting the column as text before but for some reason doing it this way solved the problem. Thank you very much, and everyone else who offered their expertise.

 

[Alex Blakenburg]

Hey, this worked! I had played around with formatting the column as text before but for some reason doing it this way solved the problem. Thank you very much, and everyone else who offered their expertise.

Just formatting the column as text does not change the data that has already been entered ie if Excel was recognising it as a number it will remain a number.
The Text to Column function converts the formatting to text and then effectively re-enters the data which is now forced to be recognised as text.
(the same is true when you are trying to go the other way and convert text to numbers)

Glad we were able to help.