Skip to content

How to Convert Comma to Decimal Point & Dot to Comma in Excel

    Download the featured file here. 

     

    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.

    1. Select the cells that contain the values you want to convert.
    2. Select the Data tab on the Ribbon.
    3. Click the Text to Columns button in the Data Tools group.
    4. In step 1 of the wizard select Fixed Width.
    5. Click Next.
    6. Click Next to skip step 2 of the wizard.
    7. In the step 3 click on the Advanced button.
    8. 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.
    9. Click on OK.
    10. 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.

    =NUMBERVALUE(A2,”,”,”.”)

    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:

    1. Select the cells that you want to apply the format to.
    2. Use the shortcut key combination CTRL 1 to open the Format Cells dialog box.
    3. Select Number in the category list.
    4. Tick Use 1000 separator().
    5. Click OK.