Forum Discussion
Why is Excel treating text as numbers?
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 !
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.
You may add helper columns and sort based on them
Result is
