How to Sort Excel Numbers That Include Units
Question details
The user needs a method to accurately sort values numerically when cells contain numbers combined with text units, such as 'Cr', 'km', or 'USD'.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing and sorting a dataset where numbers are appended with text units, causing the data import or export layout to become disorganized.
- Observed behavior
- Values containing units are stored as text by Excel, causing them to be sorted alphabetically rather than numerically from smallest to largest.
Check your dataset to ensure the units are consistent across the column. If multiple different units are mixed (like meters and kilometers), you should mathematically convert them into a single base unit before applying formatting.
Remove Units and Apply a Custom Number Format
This is the most reliable method. By removing the physical text and replacing it with a visual custom format, Excel stores the cell as a pure number while still displaying the unit.
Excel cannot perform numerical operations or properly sort data if letters are typed directly into the cell. By transitioning to a Custom Number Format, the unit acts strictly as a visual label.
Highlight the cells containing the numbers with units that you want to sort.
Press Ctrl+H to open the Find and Replace dialog. In the 'Find what' box, type the unit (e.g., ' Cr' or 'km' with any leading spaces). Leave the 'Replace with' box completely blank and click 'Replace All'.
Right-click the highlighted data range and select 'Format Cells' from the context menu, or press Ctrl+1.
Go to the 'Number' tab and select 'Custom' from the category list. In the 'Type' box, enter a format that includes your unit in quotation marks, such as `0.00 "Cr"` or `0 "km"`. Click OK.
Go to the 'Data' tab and click the 'Sort Smallest to Largest' or 'Sort Largest to Smallest' button. Your values will now sort accurately.
Separate Numbers and Units using Text to Columns
If you prefer to keep the text unit in your dataset but want to sort correctly, you can split the number and the unit into two separate columns.
Seamlessly Format and Sort Datasets in WPS Spreadsheet
WPS Spreadsheet provides powerful formatting tools that allow you to easily remove text units, apply custom display formats, and sort your numerical data with absolute precision.
- 1. Open Your Spreadsheet: Launch WPS Office and open your workbook containing the data with units.
- 2. Remove Existing Text: Select your column, press Ctrl+H, enter the unit in 'Find what', leave 'Replace with' blank, and select 'Replace All'.
- 3. Format Cells: Right-click the selected data, choose 'Format Cells', and switch to the 'Custom' category.
- 4. Set the Unit Display: Type your desired format (e.g., `0.00 "USD"`) into the Type field and apply.
- 5. Sort Numerically: Head to the Data tab and apply Ascending or Descending sort to perfectly organize your values.
Frequently Asked Questions
Why does Excel sort 10 km before 2 km?
When cells contain letters or text characters like 'km', Excel treats the entire cell as a text string. Text strings are sorted alphabetically, character by character. Since the character '1' comes before '2' alphabetically, '10 km' is placed before '2 km'.
Can I use formulas to sort numbers with units without deleting the original text?
Yes. You can use a helper column with a formula like `=VALUE(SUBSTITUTE(A2, " km", ""))` to extract just the numerical portion. You can then sort your entire dataset based on the values in this helper column.
Will custom formatting affect my underlying calculations?
No, custom formatting changes only the visual display of the cell. The underlying data remains a pure number, meaning functions like SUM, COUNT, and AVERAGE will continue to work perfectly.
