Loading...Many times you need to enter two different values into a cell sometimes both text and numbers. By default Google Spreadsheets aligns the numeric entry to the right and text entry to the left. But Spreadsheets will discard the numeric value and consider the whole input as a text value. Well you can sort numbers in Spreadsheets in a cell containing both text and numbers. Here’s how you do it.
Suppose you have a spreadsheet with numeric values followed by text in the same cell. Say your cell contains the age of your friends followed by their names. Like you have “15 Bikash” in the first cell. If you want to calculate the average ages of your friends, the formula entered won’t work because the cell values will be considered as texts only. So to extract number only from a cell, use this formula:
=LEFT(A1,FIND(” “,A1)-1)
Though the number is displayed, this formula will consider the number as a text value and so aligns it to the left of the cell(will display 15 aligned to left for the above example). But you can convert this text value to numeric value by modifying the above code a bit, by adding “+0” at the end. Then the code will be something like this:
=LEFT(A1,FIND(” “,A1)-1)*1
This will give the same result i.e. 15, but the number will be aligned to the left, making Spreadsheets to consider it a numeric value. Applying this formula for each of the cells will let you extract the numbers in purely numeric form and then do basic calculations using Spreadsheets formula. In Google Spreadsheets, you can sort numbers in all cells in a column at a time, using the following code. If you have 10 rows from A1 to A10, paste the code given below in one of the fresh cells in another column:
=LEFT(A1:A10,FIND(” “,A1:A10)-1)*1.
Now copy this cell. Then select all other rows in the column for which you want to apply the formula. Right click the selected rows and click on Paste. The formula will be applied for all of these rows in your Spreadsheet, i.e. you will have a numeric values of the ages of all of your friends.
So with this trick in Spreadsheets, you can sort numbers in a cell which follow some order and then make calculations for them.