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 English/United Kingdom locale:
Change numbers from Bengali/Bangladesh locale numbers to Arabic/Egypt locale:
Inputs
One.
Options
•Check the column(s) you wish to transform.
•Set From according to the numeric format 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): Input number are US/UK style, with . (Dot) as the decimal separator.
oEuropean , (comma): Input numbers are European style, with , (Comma) as the decimal separator.
oLocale in Preferences: Interpret input numbers using the locale the Preferences window.
oSelected Locale: Interpret input numbers using the locale in Locale from.
•Set Locale from to the locale of the input numbers. Available for Selected Locale only.
•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.
•Set Locale to according to set the decimal and group separators and digits for the numbers output.
•Check use group separators to include the group separators for the selected locale. E.g. to turn 1234567 to 1,234,567 for a English/United States or English/United Kingdom locale.
•Set Non-numeric according to what you want to do with non-numeric values in transformed columns. NaN means Not a Number.
Notes
•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