Sorting data - number/text problem

Question

I changed formats of all cells in a column to a Text, but it stays sorted as number :( How to sort it as a text, please?

Example:

78
175
205
493
515
582
1171

sorted as numbers, although format is Text

Answer 1

Hi Procházka,

Thanks for getting back to us. 

As you already have input the raw data formatted as Number, we would like to suggest using helper columns. Please insert a column near the current column you would like to sort alphabetically, pick the first cell and input for example =TEXT(A3, "###"), drag down to the bottom of the entire column, then sort A to Z, and select Expand the selection in the below window.

For more detailed information, please check the below article:

How To Sort Alphanumeric Data In Excel?

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.​​

Let us know if you need further assistance.

Thanks,

Tina

Answer 2

Weirdier and weirdier.

Seems it depends if the cells were formatted to text prior to entering values or afterwards:

1. set format of cells to Text
2. write values 299, 21, and 3 into three formated cells
3. sort them in ascending order

Result:

21

299

3

1) write values 299, 21, and 3 into three non-formated cells

2. set format of those cells to Text
3. sort them in ascending order

Result:

3

21

299

Answer 3

Hi Procházka Miloš,

Sorry about this mistake.

Here is this function in Czech: Funkce HODNOTA.NA.TEXT. 

Hope it helps.

Regards,

Alex