Sometimes, when you import data from other sources into Excel, you may encounter numbers that are formatted as text. This can cause problems when you try to perform calculations or apply formulas on them. For example, if you have a text-number like ” 123 “, Excel will treat it as a text string, not a number. You can tell if a number is formatted as text by looking at its alignment in the cell. Text-numbers are usually aligned to the left, while real numbers are aligned to the right.
One way to convert text-numbers to real numbers is to use the TRIM function. The TRIM function removes any leading or trailing spaces from a text string, and returns a trimmed text string. By applying the TRIM function to a text-number, you can remove any extra spaces that prevent Excel from recognizing it as a number.
Procedure
To use the TRIM function to convert text-numbers to real numbers, follow these steps:
- Select a blank cell where you want to enter the formula.
- Type
=TRIM(and select the cell that contains the text-number you want to convert. Alternatively, you can type the cell reference directly, such as=TRIM(A2). - Close the parenthesis and press Enter. The formula will return a trimmed text string that looks like a number.
- To convert the trimmed text string to a real number, you can either use the Paste Special option or the VALUE function.
- To use the Paste Special option, copy the cell that contains the formula, and then right-click on the same cell or another cell where you want to paste the value. Select Paste Special, and then choose Values under Paste. Click OK. This will replace the formula with the value of the trimmed text string.
- To use the VALUE function, you can nest it inside the TRIM function, such as
=VALUE(TRIM(A2)). The VALUE function converts a text string that represents a number to a numeric value. Press Enter, and the formula will return a real number.
Example
Let’s say you have a table of sales data that you imported from a CSV file. The table looks like this:
| Product | Price | Quantity | Total |
|---|---|---|---|
| A | $10 | 100 | $1,000 |
| B | $20 | 50 | $1,000 |
| C | $30 | 40 | $1,200 |
| D | $40 | 30 | $1,200 |
| E | $50 | 20 | $1,000 |
| F | $60 | 10 | $600 |
However, when you try to sum up the total column, you get a wrong result. This is because some of the numbers in the total column are formatted as text, and Excel ignores them in the calculation. You can see that the text-numbers are aligned to the left, while the real numbers are aligned to the right.
To fix this, you can use the TRIM function to convert the text-numbers to real numbers. In a blank cell, type =TRIM(D2) and press Enter. This will return a trimmed text string that looks like a number: “1000”. Then, copy the cell and paste it as values in the same cell or another cell. Alternatively, you can use the VALUE function to get the real number directly, such as =VALUE(TRIM(D2)). This will return a real number: 1000.
You can repeat this process for the other text-numbers in the total column, or use the fill handle to drag the formula down to the rest of the cells. The table will look like this after the conversion:
| Product | Price | Quantity | Total |
|---|---|---|---|
| A | $10 | 100 | 1000 |
| B | $20 | 50 | 1000 |
| C | $30 | 40 | 1200 |
| D | $40 | 30 | 1200 |
| E | $50 | 20 | 1000 |
| F | $60 | 10 | 600 |
Now, you can sum up the total column correctly, and get the result: 6000.
Other approaches
Besides using the TRIM function, there are other ways to convert text-numbers to real numbers in Excel. Here are some of them:
- Use the Text to Columns feature. This feature allows you to split a text string into multiple columns based on a delimiter, such as a comma, a space, or a tab. By using this feature, you can also convert text-numbers to real numbers by choosing the General format for the destination column. To use this feature, select the column that contains the text-numbers, and then go to the Data tab and click Text to Columns. Follow the steps in the wizard, and choose the appropriate options for your data.
- Use the Find and Replace feature. This feature allows you to find and replace a specific text or character in a range of cells. By using this feature, you can remove any extra spaces from the text-numbers by replacing them with nothing. To use this feature, select the range of cells that contains the text-numbers, and then press Ctrl+H to open the Find and Replace dialog box. In the Find what box, type a space, and leave the Replace with box blank. Click Replace All, and the spaces will be removed from the text-numbers.
- Use a formula that performs a mathematical operation on the text-number. This method works because Excel will try to convert the text-number to a real number before performing the calculation. For example, you can use a formula like
=A2+0or=A2*1to convert the text-number in cell A2 to a real number. However, this method may not work for text-numbers that contain non-numeric characters, such as commas, dollar signs, or percentage signs.
