Formatting for Excel Calculations
It’s a major source of incorrect results: Numbers formatted as text. For example, mathematical functions won’t work anymore for instance, after importing data into Excel from sources such as the internet. Check out the VALUE function!
There are times, when you import data into Excel from other sources such as the internet or other applications, data types are inadvertently changed. Often, Excel stores numeric data as text which is not desirable: data in these columns cannot be used in mathematical or statistical functions such as SUM, AVERAGE, etc. and use of these functions results in errors. We will look at a method to convert the text to numbers using the VALUE function. Subsequent use of mathematical or statistical functions will result in the correct result.
Here is a quick tutorial on the use of the VALUE function to convert text to numbers.
Text or Number? - A quick check
When a column in a worksheet holds text, by default the values are aligned to the left. If they are numbers, then the values are aligned to the right by default. This is a quick way to identify the data types.
In the image above, both columns are aligned on the left side because they are formatted as text. The column labeled Units should have been numeric data.
VALUE function
DESCRIPTION
Takes the value of a cell in text format and returns the value as a number, where Text is the data to be converted. This can either be a cell reference or the actual text in double quotes.
SYNTAX
EXAMPLE
1. Enter the data into Column B and C as shown above – as you enter each number in Column C, typea single quote ( ‘ ) in front of the valueand turns it into text. C2 – ‘123 C3- ‘456 etc.
2. Now type the formula =SUM(C2 : C12) into cell C13. Instead of the expected sum, you will get an answer of zero. This is because the values being added are text and thus ignored by the SUM function.
3. Click on cell D2 and type the formula =VALUE(C2). This function is one of the Text functions of Excel. The numeric equivalent of the cell value in C2 will appear in cell D2, and thus it should be right-aligned by default.
4. Copy the formula to the remaining cells in that column up to D12 by dragging on the fill handle.
5. Now type the formula =SUM(D2 : D12) into cell D13. Since the values being added are numbers, the correct answer will be displayed.
I hope that this tutorial provided a satisfactory answer to the question, as to how to quick-fix wrongly formatted numbers in Excel. However, from an IT point of view one question is even more intriguing: How come you have wrongly formatted numbers in your Excel spreadsheet in the first place? We strongly recommend thatyou take a closer look at the import process, that’s generally where this issue arises. You’ll find some useful advice on state-of-the-art import methods on our website in the section on Excel Reporting.(https://www.solutiontogo.de/)