CARA MENGGANTI KOMA MENJADI TITIK DI EXCEL

Have you ever needed to convert decimal numbers in Excel from comma-separated to dot-separated or vice versa? It is a common issue that many users encounter when working with spreadsheets. Fortunately, there are simple ways to convert decimal separators in Excel, and we’ll walk you through them in this article.

How to Change Decimal Separators in Excel

To change decimal separators in Excel, follow these steps:

1. Select the cells that contain the decimal numbers you want to convert.
2. Click on the “File” tab in the ribbon menu.
3. Click on “Options” at the bottom of the menu.
4. Click on “Advanced” in the left pane of the Excel Options dialog box.
5. Scroll down to the “Editing options” section.
6. Find “Decimal separator” and select the separator you want to use: period or comma.
7. Click “OK” to apply changes and close the dialog box.

Now your decimal separators in the selected cells will be changed accordingly.

How to Convert Decimal Separators for the Entire Workbook

If you want to convert decimal separators for the entire Excel workbook, there are a couple of options:

Option 1: Use Windows Regional Settings

This option changes the decimal separator for all Excel workbooks but affects other applications as well.

1. Open the “Region” settings in Windows Control Panel.
2. In the “Formats” tab, click on “Additional settings”.
3. In the “Numbers” tab, change the decimal symbol to period or comma.
4. Click “OK” to save changes.

Now, whenever you open a new Excel workbook or create a new one, the decimal separator will be the one you’ve set in the Windows Regional Settings.

Option 2: Use VBA Macro

This option requires some knowledge of VBA programming but allows changing decimal separators for a specific Excel workbook only.

1. Open the Excel workbook you want to modify.
2. Press Alt + F11 to open the Visual Basic Editor.
3. Click on “Insert” in the menu bar and choose “Module”.
4. Type the following VBA code in the module:

Sub ChangeDecimalSeparator()
    Dim MyArray() As Variant
    Dim MyRange As Range
    Dim cell As Range
    Dim i As Long, j As Long

    MyArray() = ActiveWorkbook.Windows(1).SelectedSheets.UsedRange
    Set MyRange = ActiveWorkbook.Windows(1).SelectedSheets.UsedRange
    For i = LBound(MyArray, 1) To UBound(MyArray, 1)
        For j = LBound(MyArray, 2) To UBound(MyArray, 2)
            Set cell = MyRange.Cells(i, j)
            If IsNumeric(cell.Value) Then
                If InStr(cell.Value, ".") > 0 Then
                    cell.Value = Replace(cell.Value, ".", ",")
                ElseIf InStr(cell.Value, ",") > 0 Then
                    cell.Value = Replace(cell.Value, ",", ".")
                End If
            End If
        Next
    Next
End Sub

5. Press F5 or click on the “Run” button in the toolbar to execute the macro.
6. Choose the decimal separator you want to use.
7. Click “OK” to apply changes.

This VBA macro will loop through all the cells in the selected range and replace periods with commas or commas with periods, depending on your preference.

FAQ

Q: Why do I need to change decimal separators in Excel?

A: If you work with international teams or partners, you may encounter different decimal separators depending on the country or region. For instance, the United States, the United Kingdom, and Australia use a period as a decimal separator, while most European countries use a comma. Changing decimal separators in Excel ensures consistency and accuracy in your calculations.

Q: Can I use a formula to change decimal separators in Excel?

A: While Excel provides several built-in functions to manipulate text strings, there is no direct formula to change decimal separators. However, you can use a combination of the SUBSTITUTE and VALUE functions to replace periods or commas with each other:

=VALUE(SUBSTITUTE(A1,",","."))

This formula converts a decimal number in cell A1 with a comma separator to a number with a period separator. To convert from period to comma, simply swap the arguments:

=VALUE(SUBSTITUTE(A1,".","."))

Video Tutorial

For a visual demonstration of how to change decimal separators in Excel using the Excel Options dialog box and the VBA macro, watch this video tutorial: