This column operation converts all numbers stored as text strings into numeric values. By doing this, mathematical operations can be performed on values in this column when, as numbers stored as text, these actions would otherwise be considered invalid.
Numbers stored as strings appear left-aligned within a cell and in black text; numbers stored as numeric values are right-aligned and appear in green.
When this operation is applied to cells that cannot be converted to numeric values, it will have no effect. In a column with both text and numbers on different rows, only those rows that can be converted will be changed.
If a value that appears suitable for conversion is not successfully converted, it is likely that there are non-number characters somewhere in the cell.
Some examples of characaters that can inhibit the transformation:
- leading or trailing spaces. These can be removed by using the column opreation for "White Space trim leading and trailing" before your apply the "Transform into numeric operation".
The "White Space trim leading and trailing" operation examines all rows for spaces at both the beginning and end of the text string. Where it finds them, they are removed—leaving only the value in the cell.
- intermediate characters (such as commas or spaces.) Operations such as Column split or a Compute Column that uses REGEX may be required, first, in order to successfully create a column of numeric values.
Note: A single period (“.”) in a cell of numbers will be interpreted as a decimal point. These strings will be able to be converted into numeric values without requiring any other operations.