Convert numbers stored as text to numbers
If you see cells with green triangles and numbers that are aligned to the left, then you have numbers stored as text. This can cause unexpected results and formatting problems. In addition when formulas are stored as text, the formula won't show the final result as a number. Instead, it will display as text and might look "frozen in time." The following process will show you how to convert these cells to numbers.
1. Select a column
Select a column with this problem. If you don't want to convert the whole column, you can select one or more cells instead. Just be sure the cells you select are in the same column, otherwise this process won't work.
2. Set the format
Press CTRL + 1 (or 
+ 1 on the Mac). Then select any number format or currency format.
3. Click Data > Text to Columns
On the Data tab, click Text to Columns.
4. Click Finish
Click Finish right away and Excel will convert the cells.
Note: If you still see formulas that are not showing as numeric results, then you may have Show Formulas turned on. Go to the Formulas tab and make sure Show Formulas is turned off.
Other ways to convert:
Use a formula to convert from text to numbers
You can use the VALUE function to return just the numeric value of the text.
1. Insert a new column
Insert a new column next to the cells with text. In this example, column E contains the text stored as numbers. Column F is the new column.
2. Use the VALUE function
In one of the cells of the new column, type =VALUE() and inside the parentheses, type a cell reference that contains text stored as numbers. In this example it's cell E23.
3. Rest your cursor here
Now you'll fill the cell's formula down, into the other cells. If you've never done this before, here's how to do it: Rest your cursor on the lower-right corner of the cell until it changes to a plus sign.
4. Click and drag down
Click and drag down to fill the formula to the other cells. After that's done, you can use this new column, or you can cut and paste these new values to the original column. Here's how to do that: Select the cells with the new formula. Press CTRL + X. Click the first cell of the original column. Then on the Home tab, click the arrow below Paste, and then click Paste Special > Values.
Use Paste Special and Multiply
In this technique, you multiply each cell by 1 in order to convert from a text number to a regular number. Because you're multiplying the contents of the cell by 1, the result in the cell looks identical. However, Excel actually replaces the text-based contents of the cell with a numerical equivalent. This method is handy because other methods require the cells to be in one column, but this method does not.
-
Select a blank cell that doesn't have a green triangle and isn't aligned to the left.
-
Press CTRL + 1.
-
On the Number tab, click General, and then click OK.
-
In the cell, type 1, and then press ENTER.
-
Select the cell, and then press CTRL + C to copy the value.
-
Select the cells that contain the numbers stored as text that you want to convert.
-
On the Home tab, click the arrow below Paste, and then click Paste Special.
-
Under Operation, select Multiply, and then click OK.
Are you fed up listening to people who could not trace your house and street number? Does the existing number painted on the entrance fail to get noticed a lot? A fool-proof solution to all these above problems is provided herewith. By applying this solution, people can now view your house number from a reasonable distance. This article will guide you how to five area codes you should never answer design a house number on tiles and append them to your outer walls.
ReplyDelete