5 Quick Ways to Remove Dots in Excel

Using Conditional Formatting

One of the simplest ways to remove dots in Excel involves using the powerful tool known as Conditional Formatting. This method allows you to highlight cells containing dots and then replace them with a blank space or another character without changing the actual data:

• Select the range of cells where you want to eliminate the dots.
• Go to the Home tab on the Excel Ribbon, then click on Conditional Formatting.
• Choose New Rule from the dropdown menu.
• Under Select a Rule Type, opt for Use a formula to determine which cells to format.
• In the formula box, enter a formula like =SEARCH(".", A1) > 0 where A1 is the first cell in your selected range. This formula searches for the presence of a dot within the cell's content.
• Click on Format, go to the Number tab, choose Custom from the list, and type in this custom format: 0;-0;;@. This will effectively remove dots in numbers while maintaining other characters.
• Click OK twice to close the dialog boxes.
• All numbers with dots should now appear without them. However, remember this is a visual change; the data in the cells remains unchanged.

Using Find and Replace

The Find and Replace tool in Excel is perhaps the most straightforward method for quickly removing dots:

• Select the cells where you want to remove the dots.
• Press Ctrl + H to open the Find and Replace dialog.
• In the Find what box, enter a period (.).
• Leave the Replace with box empty, or if you want to replace dots with another character like a comma or space, enter it here.
• Click on Replace All to remove all instances of dots in the selected range. Excel will show you how many replacements were made.
• Click Close to dismiss the dialog box.

Note:

⚠️ Note: If your data contains decimals, this method will remove the decimal point, altering the value.

Using SUBSTITUTE Function

Excel’s SUBSTITUTE function can also be used to remove dots by replacing them with nothing or another character:

• Assuming your data is in cell A1, use the formula: =SUBSTITUTE(A1, ".", "")
• This formula searches for the period character (.) in cell A1 and replaces it with an empty string, effectively removing it.
• Copy and paste this formula down the column as needed or use it within larger formulas.

Using Text to Columns

Text to Columns can be a useful tool if you’re dealing with data where the dots separate distinct pieces of information:

• Select the column where you want to remove dots.
• Go to Data > Text to Columns.
• Choose Delimited and click Next.
• Check the Other checkbox and enter a period (.) in the box next to it.
• Click Finish. Excel will split the text at each dot.
• If you don't need the new columns created by the split, you can delete or modify the formula to join the separated parts back together without the dots.

Using VBA

For those with some familiarity with Visual Basic for Applications (VBA), you can write a macro to remove dots:

Sub RemoveDots()
    Dim cell As Range
    For Each cell In Selection
        If InStr(1, cell.Value, ".") > 0 Then
            cell.Value = Replace(cell.Value, ".", "")
        End If
    Next cell
End Sub

• Press Alt + F11 to open the VBA editor, insert a new module, and paste the above code.
• Run the macro by selecting the cells where you want to remove dots and then executing the macro through Excel's Developer tab or with a shortcut key.

🔎 Note: This method will permanently change your data, so ensure you have backups or are comfortable with the change.

In summary, there are several effective ways to remove dots from data in Excel. Each method has its own advantages and considerations, from visual formatting changes with Conditional Formatting, to direct data alterations with Find and Replace, to using formulas like SUBSTITUTE or even writing VBA code. Choosing the right method depends on your need for preservation of the original data, the complexity of your data, and your familiarity with Excel's features. Whether you're cleaning up numbers, preparing data for analysis, or simply trying to make your data look cleaner, Excel provides flexible tools to achieve your goals efficiently.

Can I use these methods to remove dots from part of the data in Excel?

+

Yes, you can select specific cells or ranges in Excel to apply any of these methods to remove dots only from the selected data.

Will removing dots with these methods affect the formatting of my numbers?

+

Conditional Formatting and Text to Columns can preserve your number formatting, but Find and Replace, SUBSTITUTE, and VBA will change the actual data, potentially altering number formatting if decimals are involved.

Is there a way to undo the dot removal if I change my mind?

+

Undoing the changes depends on the method:

• Conditional Formatting: You can always revert the format by clearing the rule.
• Find and Replace: Use Ctrl+Z or manually replace back the removed characters.
• SUBSTITUTE Function: If it’s within a formula, you can adjust the formula or revert to the original cell content.
• VBA: You’ll need to have a backup of your data, or manually input the dots back.