Four Quick and Easy Ways to Convert Numbers Stored as Text to Real Numbers in Excel
If you store numbers in Excel that you need to use in formulas, it’s important to make sure they are actually numbers and not just text like 50 or 50 thousand. You can easily convert these types of numbers into actual values using one of these four techniques.
Use the COUNTIF formula
If you have a column of numbers that are stored as text, you can use the COUNTIF formula to count the number of cells that contain numbers. To do this, simply enter the following formula into a cell: =COUNTIF(A1:A10,*) You will see how many cells in A1:A10 have real numbers.
The second method is to use the FIND function: You can also find out which cells have real numbers by using the FIND function. Simply type the following into a cell: =FIND(*,A1:A10) Replace * with the operator you want to search for (i.e., =). Replace A1:A10 with the range of cells you want to search through. Lastly, if you know what your ranges are called, then you can simply create an IF statement: Lastly, if you know what your ranges are called, then you can simply create an IF statement to check whether or not a cell contains a value. For example: =IF(A1=100,100,) would tell us whether or not 100 was stored in A1.
Use Conditional Formatting
One way to convert numbers stored as text to real numbers is to use conditional formatting. To do this, select the cells you want to format, then go to the Home tab and click on Conditional Formatting. Next, select Highlight Cell Rules and then Greater Than. This will format all the cells that are greater than the number you specify as real numbers. If you change the value of less than or greater than, it will change how many cells are formatted accordingly. For example, if I change the value to greater than 4999, it would format all cells with a value greater than 5000 as real numbers. The only drawback to this method is that the formatting can be removed by clicking on Clear Rules at the bottom of the window.
To remove all conditional formatting from your spreadsheet, follow these steps: Select Conditional Formatting > Clear Rules > OK. Now when you open your spreadsheet, none of your cells will have any type of highlighting applied to them. You can highlight specific cells again by going back into Conditional Formatting and following these same steps.
Alternatively, another way to format just one cell is to right-click on the cell and choose Number Format > More Formats > Scientific (8 decimal places). That will apply that number formatting just for that one cell. However, if you want to make this number formatting for more than one cell, double-click the border between those cells until they’re highlighted and repeat these steps.
Look for common patterns
If you have a column of numbers that are stored as text, there are a few quick and easy ways to convert them to real numbers. The first way is to use the built-in function, VALUE. Simply select the cells you want to convert and enter =VALUE(cell) in the formula bar, then press Enter. Another way is to use the Text to Columns feature. First, select the cells you want to convert, then go to Data > Text to Columns.
Select Delimited with Comma from the drop-down menu at the top, and click Next. You will now see an Input Text box on your screen where you can type a comma or tab between each number. Once you’re done entering all your numbers into this box, click Finish. Now when you look at your spreadsheet, instead of seeing long strings of letters in some columns and numbers in others, you’ll see neat little rows with digits everywhere!
Use a Custom Function
If you have a lot of data that you need to convert from text to numbers, you can use a custom function. This will take some time to set up, but it will save you a lot of time in the long run. Plus, it’s easy to use and you can customize it to your needs. You just copy this custom code into your worksheet: =FIXED(A1). Then replace A1 with whatever cell contains the value you want to convert. For example, if A1 has 8 then FIXED(A1) will equal 8. If A1 has 9999999999, then FIXED(A1) will equal 9999999999. The same goes for any other values stored in cells that you might enter manually or import from another source.
