Column sorting

[lastknownuser]

Hi! I have a question, I need special sorting for one column, I have:

I need:

So column B sorted by the first 4 digits, and at 5&6 digits is number 99.
Any help would be very welcome.

 

[Alex Blakenburg]

I am unsure of what your issue is.

Your data is left aligned indicating your numbers are being treated as text.
As long as when you sort you tick the box that says:-
"Sort numbers and numbers stored as text separately"
It should give you exactly what you are indicating you want to finish up with.

 

[lastknownuser]

I tried this way, I'm getting this

It still sees 6 digits numbers (formatted as text here yes) as bigger than 4 digit ones
I could add 00 to all 4 digits number and then sort, and remove 00 later, but thought maybe there is another way

 

[Alex Blakenburg]

To sort the way you want them, they all need to be entered as text.
The ones with the green triabugle on them are probably text but the others are not.

How is the data being populated, manual input, copy paste, an import ?
Note: once they are text, you won’t be able to perform numeric operations with them.

 

[lastknownuser]

They are all text, I formated the whole column B
This is just basic hand unput for example

 

[Alex Blakenburg]

Your sort is indicating otherwise. Quickest way to check, format the column as number with 2 decimal places. The numbers that change are numbers, the others are text.

To input numbers as text, the column has to formatted as text BEFORE you enter the numbers. Changing it afterwards will not convert the numbers to text.

 

[lastknownuser]

That did it! I didn't know that changing afterwards they are not converted, thanks for help!

 

[Joe4]

That did it! I didn't know that changing afterwards they are not converted, thanks for help!

Note that you can convert/fix all the "text" entries to numbersat once by highlighting the column, going to the Data menu, and selecting "Text to Columns", then clicking Finish.
This is the equivalent of "re-entering" all the data at once.

 

[Alex Blakenburg]

That did it! I didn't know that changing afterwards they are not converted, thanks for help!

Thanks for letting us know. Glad we could help.

PS: regarding @Joe4's comment, he described what it the more common scenario where users want to convert numbers from being text to a number (often due to extra spaces being included).
You are actually trying to go the other way and convert it to text. Text to Columns can do that too, you just need to select Text in the box shown.

 

[Joe4]

Thanks for letting us know. Glad we could help.

PS: regarding @Joe4's comment, he described what it the more common scenario where users want to convert numbers from being text to a number (often due to extra spaces being included).
You are actually trying to go the other way and convert it to text. Text to Columns can do that too, you just need to select Text in the box shown.

View attachment 56694

Ah yes, I had it backwards.
You are right, very seldom do we see it go that way.
Thanks for the clarification and correction to show them how they can use Text to Columns to go that way too.