Forum Discussion
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.
7 Replies
Try a Custom Sort. Perhaps that works for you. Let's assume your ProductID's (before sorting and make sure that they are all texts) look like this:
Custom Sort as shown above and press OK to get this:
Now select the second sort options in the Sort Warning and press OK to get this:
As long as all ProductID's are really texts you should get the correct order. If not, best to start a new thread and share an example (upload an xlsx file or share a link that gives access to it on OneDrive or similar).
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 !
fredI have a column of product ID numbers stored as text, with either 11 or 17 digits. Each longer number contains a "suffix" of 6 digits associating it with an 11-digit number. I need to sort the column so each 11-digit product is followed by its associated 17-digit products. But invariably Excel sorts all 11-digit numbers first in numerical order, followed by all 17-digit numbers. The text-to-column process didn't help.
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 !
