In this video I demonstrate how to convert European formatted numbers to UK format. For example in the data displayed below the thousand separator is a full-stop and the decimal separator is a comma.
For a UK format we would want these the other way around – comma for decimal separator and full-stop as a decimal separator.
In the video I demonstrate two ways of achieving this in Excel.
Method 1: Text to Columns
The first method uses Text to Columns. To use this method follow these steps.
- Select the cells that contain the values you want to convert.
- Select the Data tab on the Ribbon.
- Click the Text to Columns button in the Data Tools group.
- In step 1 of the wizard select Fixed Width.
- Click Next.
- Click Next to skip step 2 of the wizard.
- In the step 3 click on the Advanced button.
- In the Advanced Text Import Settings specify which separators are currently being used in your data. Enter the character that is being currently used for the decimal and thousands separator.
- Click on OK.
- Click on Finish.
Method 2: Use the NUMBERVALUE Function
The NUMBERVALUE function converts text to a number, in a locale-independent way.
NUMBERVALUE(Text, [Decimal_separator], [Group_separator ])
The NUMBERVALUE function syntax has the following arguments.
- Text Required. The text to convert to a number.
- Decimal_separator Optional. The character used to separate the integer and fractional part of the result.
- Group_separator Optional. The character used to separate groupings of numbers, such as thousands from hundreds and millions from thousands.
In our example, we would use the NUMBERVALUE function in the following way.
Our Text is in cell A2.
In our Decimal_separator argument we specify a comma.
In our Group_separator argument we specify a full-stop.
We may need to apply formatting to show the thousand separators. To do this:
- Select the cells that you want to apply the format to.
- Use the shortcut key combination CTRL 1 to open the Format Cells dialog box.
- Select Number in the category list.
- Tick Use 1000 separator().
- Click OK.