Can we sort numbers formatted as text without manually changing the formatting of them in Google Sheets? If yes, how?
In Google Sheets, there are two types of built-in sort options. One is formula-based, and the other one is command based. Other than these, we may be able to do the same (sorting) with Apps Script.
There are mainly three functions that have the sorting capability. You can read about them in my earlier Google Sheets tutorial here – Sort Data in Google Sheets – Different Functions and Sort Types.
In this specific tutorial, I will use the SORT function to sort numbers formatted as text in Google Sheets. I won’t manually change the formatting of the numbers formatted as text.
I have three options to offer you to format text-formatted-numbers in Google Sheets. Here are them.
Sorting Numbers Formatted as Text Using VALUE and SORT
First, let’s see what happens when we sort a text-formatted set of numbers using the usual sorting method.
The SORT formula in cell D2 considers the numbers as text and sorts them accordingly.
The numbers to sort are in A2:A12, which are formatted as text. Actually, for our testing purpose, I have formatted the numbers in A2:A12 to text from the menu Format > Number > Plain text.
But sometimes, when you have imported data in Google Sheets, you will get numbers in text format (left-aligned by default). You can convert them to numbers from the format menu.
But my method is useful when you don’t want to change the formatting of the imported data. Here comes the relevance of this tutorial.
Here is the formula to sort numbers formatted as text using the VALUE function with SORT in Google Sheets.
=sort(value(A2:A12))
The VALUE function eliminates the sorting issues by converting the values to numbers from text. So the SORT has no problem in properly sorting the values.
Asterisk Operator – The Alternative to VALUE
Here is an alternative to the above. Knowing this will also be useful as I have used this conversion method in many of my formulas earlier.
You can replace the VALUE here in the sorting as below.
=sort(A2:A12*1)
The result will be the same as with the earlier VALUE + SORT example.
By multiplying the ‘text’ in the range A2:A12 with 1, we convert the values in A2:A12 to numbers.
Note:
You may have come across different sheets and formulas inserted by advanced Google Sheets users. Have you seen the use of To_Text with Query?
The To_Text will change the formatting of the whole data set to text format.
Since Query has an issue with mixed-type data, in some rare cases, we use the To_Text to format the whole dataset or a particular column to text.
If you have such output in hand and want to convert any column that contains numbers formatted as text, you can use the above two methods or the one below.
TEXT Formatting to Sort Numbers Formatted as Text
Here is another method. In this method, I am going to use the TEXT function.
Here, for sorting, we won’t format the numbers formatted as text back to numbers. Instead, we will text format the values as below to use as the sort range.
=ArrayFormula(text(A2:A12,"0000.00"))
Note:
We will remove the ArrayFormula when using this range inside the SORT formula as it’s not necessary within another array formula like SORT for sorting.
Here you can replace the format “0000.00” with “00000.000” depending on the number of digits in your list. Now to the sort formula.
In SORT, we can specify a range as the sort column by strictly following the below sort standard in Google Sheets.
The above ArrayFormula is the (sort) range. It must be a single column with the same number of rows as the range (A2:A12). The above array formula adheres to this standard rule.
=SORT(A2:A12,text(A2:A12,"0000.00"),1)
The above formula has one benefit over the other two formulas. It retains the formatting of the numbers in A2:A12 in the result.
Benefit of Using the Above Method (Example)
I have Product names in A2:A12 and their Quantities in B2:B12. I want to combine the product names with quantities and arrange them quantity-wise.
Without using any helper column (additional column), we can do that using the below formula.
=SORT(A2:A12&"-"&B2:B12,text(B2:B12,"0000.00"),1)
You may find this last tip useful while writing advanced formulas.
That’s all about how to sort numbers formatted as text in Google Sheets.
Thanks for the stay. Enjoy!
To learn more about Google Sheets and enhance your spreadsheet skills, visit Crawlan.com
