Forum Discussion
Solved
Why is Excel treating text as numbers?
Hello ! I know that most of the poeple are requesting the opposite but it's an issue for me. I have this ID column that contains , well, ID like 1 2 3 10 10.1 10.2 and so on.
I want them to be sorted alphabeticaly. I should get 1 10 10.1 10.2 2 . But Excel decided otherwise. even with a text format of the colum, it proposes a smallest to largest sort instead of a A to Z .
how can I forbid that and force the alphabetical sorting ?
thanks in advance
fred
ps)no, I cannot edit the value and add a space in
Fsim Since you say you can't edit the values, I assume they come from some kind of import. If so, where from? Upon importing data from e.g. a txt or csv file you need to explicitly indicate the data type of the columns that get imported.
Try this. Select the ID column. On the data ribbon, select Text-to-columns. Advance to step 3 changing nothing. In step 3, set the column data format to Text and press Finish. That should solve the problem.
Fsim Since you say you can't edit the values, I assume they come from some kind of import. If so, where from? Upon importing data from e.g. a txt or csv file you need to explicitly indicate the data type of the columns that get imported.
Try this. Select the ID column. On the data ribbon, select Text-to-columns. Advance to step 3 changing nothing. In step 3, set the column data format to Text and press Finish. That should solve the problem.
- hi Riny !
indeed the text to column trick worked fine ! thanks ALOT !
fred
What are we talking about here? If I use the Excel 365 function SORT to create a sorted list it creates an alphabetic sort if the input is formatted as text (with a little green warning in the top left of the cell warning of numbers formatted as text).
If required, it is even possible so sort numbers alphabetically using
= SORTBY(numbers, TEXT(numbers, "General"))If, however, we are talking about interactive use of the SORT ribbon command then one has a choice of sort strategy
- Hello Peter, I was talking about the interactive sorting indeed. and I do not have this warning. Only sort from the smalest to the larget. Hopefully the solution from Riny worked .
thanks anyway !
