Cara Merapikan Nomor HP Ada Pemisah Tanda Strip (-) atau Spasi di Excel

Introduction

In this tutorial, we will learn how to tidy up mobile phone numbers in Excel that contain separators such as dashes (-) or spaces. Using the SUBSTITUTE function, we will remove unwanted characters to present a clean list of phone numbers. This is particularly useful for data organization and analysis in Excel.

Step 1: Open Your Excel File

• Download the practice file from the provided link: Download Excel File.
• Open the file in Microsoft Excel.

Step 2: Identify the Column with Phone Numbers

• Locate the column that contains the phone numbers you want to clean.
• Make sure to note any patterns in how the numbers are formatted (e.g., with spaces, dashes, etc.).

Step 3: Use the SUBSTITUTE Function

To clean the phone numbers, we will use the SUBSTITUTE function. Here’s how:

1. Select an empty cell next to the first phone number.

2. Enter the following formula to remove dashes:

   =SUBSTITUTE(A1, "-", "")
   

   Replace A1 with the actual cell reference of the phone number.

3. Press Enter to see the result.

4. To remove spaces, use another SUBSTITUTE function:

   =SUBSTITUTE(B1, " ", "")
   

   Here, B1 refers to the cell containing the result from the previous step.

5. You can combine both actions into a single formula:

   =SUBSTITUTE(SUBSTITUTE(A1, "-", ""), " ", "")
   

Step 4: Apply the Formula to the Entire Column

• Click on the cell with the formula you just created.
• Drag the fill handle (a small square at the bottom-right corner of the cell) down to apply the formula to the other cells in the column.

Step 5: Copy and Paste Values

• Once you have cleaned all phone numbers, you might want to replace the original column with the cleaned data.
• Select the cleaned column, right-click, and choose "Copy."
• Right-click on the original column and select "Paste Special," then choose "Values" to replace the original data.

Conclusion

In this tutorial, we have learned how to clean up mobile phone numbers in Excel effectively using the SUBSTITUTE function. By removing unwanted characters like dashes and spaces, you can maintain organized and usable data. For further learning, consider exploring additional Excel functions or techniques available in the resources linked above. Happy Excel learning!