Sometimes in a column, the cells will contains both numbers and texts. Here we will provide two methods to sort a column with numbers and texts.
Sometimes there will be numbers and texts in the same cells in a column. If you need to sort this column, you will meet with problems. The order of the column will not be “1,2,3,4,5..”, but it will be “1,10,11,12,…2,21,22…”. Therefore, this kind of order will make your worksheet in a mess. In order to solve such a problem and sort the column with the normal order, you can use the two methods below to sort a column.
Method 1: Text to Columns
- Select the column in the worksheet.
- And then click the tab “Data” in the ribbon.
- After that, click the button “Text to Columns” in the toolbar. Then you will see the window of wizard pop up.
- In the window, choose the option according to the actual worksheet. Here we will choose the “Delimited”.
- And then click the button “Next”.
- In step two in the window, choose the option “Space”. If there are commas or other delimiters, you can also set by yourself.
- And then still click “Next”.
- After that, click the button “Finish” in the window. When you finish the setting, you will see the new contents in the worksheet.
- In this step, select the whole range.
- And then click the tab “Home” in the ribbon.
- After that, click the button “Sort & Filter” in the toolbar.
- In the drop-down menu, choose the “Sort Smallest to Largest”.
And now the order of the range will be “1,2,3…”.
- Next you need to connect those contents. Input the formula into the cell G1 in this example.
=A1& ” ” &B1&” ” &C1 &” “&D1& ” “& E1& ” “&F1
The “&” will connect the contents in the cells. And remember there are spaces between the quotation marks
- After that, press the button “Enter” to show the result.
- Then drag the fill handle of this cell downwards and fill this formula in the whole column.
Until now, you have finished the setting. And the order of the column will be normal.
Method 2: Excel Functions
In this method, you will use the Excel function to extract numbers from cells.
- Click a blank cell in the worksheet. Here we will click cell B1.
- And then input the formula into cell:
=LEFT(A1,2)
Because all the numbers in this column is no more than 100, we can use the LEFT function. And the second argument is 2 in the formula.
- And then press the button “Enter”. Therefore, the first number will appear in the cell.
- After that, double click the fill handle of the cell and fill this column with the same formula. All the numbers will appear in the column B.
- And then select the new range that contains column A and column B.
- In this step, sort the range from smallest to largest. Therefore, the original column will also be in the new order.
- Now delete the column B. And you will also get the result in the worksheet.
Using the LEFT function to extract numbers from cells can also help you finish the task easily and quickly.
A Comparison of the Two Methods
The above two methods will both extract the numbers and then sort a column. However, there still exist differences between the two methods. The table below shows the advantages and the disadvantages of the two methods.
|
Comparison |
Text to Columns |
Excel Functions |
|
Advantages |
By using this method, you don’t have to perform complex functions to extract numbers from cells. | There will only be one additional column in the worksheet. You can make your worksheet clean by simply deleting the second column. |
|
Disadvantages |
If you need to use the column in other range, you need to copy the new range and paste as values. As a result, there are more steps in this method. | When the forms of numbers are different, you have to develop more complex formula to extract numbers. This will cause more time and energy. |
After you reading the table, you must have a deep understanding of the two methods. The next time when you need to sort a column that contain values with numbers and texts, you will know which methods you can use.
Ensure the Excel Security in Your Computer
Excel security is very essential for you. All the data and information in Excel files should be taken care of. Therefore, you need to prepare an Excel repair tool at hand. When you meet with Excel corruptions, you can use this tool immediately. This tool is able to repair damaged xls and solve other Excel problems. If you repair Excel quickly, you will ensure that your Excel files are safe.
Author Introduction:
Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc file problem and outlook repair software products. For more information visit www.datanumen.com