Description
Change the number format in one or more columns.
Examples
Set numeric values to 2 decimal places:
Set numeric vales to the shortest accurate number:
Change European style numbers to US/UK style locale numbers (Preferences>General>Locale set to United States):
Inputs
One.
Options
•Check the column(s) you wish to transform.
•Set Decimal symbol from according to the decimal separator in the input dataset:
oAutomatic: Make an intelligent guess for each column, based on the input data. This is usually the best choice.
oUS/UK . (dot): Use . (Dot) as the decimal separator.
oEuropean , (comma): Use , (Comma) as the decimal separator.
oLocale in Preferences: Use the decimal separator from Locale in the Preferences window.
•Set Format to to the new number format (see below).
Format to |
Meaning |
|---|---|
e |
Format as [-]9.9e[+|-]999. E.g. 1234567.89 is shown as 1.235e+06. |
E |
Format as [-]9.9E[+|-]999. E.g. 1234567.89 is shown as 1.235E+06. |
f |
Format as [-]9.9. E.g. 1234567.89 is shown as 1234567.89. |
g |
Use e or f format, whichever is the most concise. |
G |
Use E or f format, whichever is the most concise. |
s |
The shortest accurate representation for the given number without exponents. E.g. 1234567.00 is shown as 1234567. |
•Set Precision to to the precision desired.
oFor the e, E, and f formats, Precision to represents the number of digits after the decimal point.
oFor the g and G formats, Precision to represents the maximum number of significant digits (trailing zeros are omitted).
o For the s format Precision to is ignored.
•Check use group separators to include the group separators for your locale. E.g. to turn 1234567 to 1,234,567 for a UK or US locale.
•Set Non-numeric according to what you want to do with non-numeric values in transformed columns. NaN means Not a Number.
Notes
•The locale is used to decide how the number is output (e.g. group and decimal separators).
•Numbers should be base 10 (decimal).
•$, £ and € characters are ignored.
•If Decimal symbol from is set to Locale in Preferences the allowed group separators are set by the locale. Otherwise Dot (if not used as the decimal separator), Comma (if not used as the decimal separator), whitespace (including Thin Space) and Apostrophe characters are ignored.
•You can also use Extract and Pad to change the number of characters.
•Warnings are shown in the Warnings tab for non-numeric values.
See also
•Video: How to convert decimal separators and number formats