Removing Spaces around Dashes

Gilbert needs to process workbooks created by various people in his company. His biggest task seems to be removing any spaces surrounding a dash within text. Some people put spaces before the dash, some after, and some on both sides. Kenton wonders if there is an easy way to remove these spaces.

There are several ways this can be accomplished. First, of course, you could use Find and Replace to remove the spaces. Just do individual searches for a space before a dash and then a search for a space after a dash. In both cases, replace what is found with a single dash.

If you don't mind using a helper column, and the cells should not contain any spaces, then you can use the following:

=SUBSTITUTE(A1," ","")

If there are other spaces allowed in the cell, then you need to make sure that you only check for spaces around the dashes. Here's a longer formula that will do the trick:

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

Perhaps the coolest helper-column approach, though, is available for those using Excel in Microsoft 365:

=REGEXREPLACE(A1,"\s*-\s*","-")

The benefit of this formula is that it removes multiple spaces before and after dashes. Plus, it will remove non-breaking spaces, which is something the previous formulas will not do. No other spaces in the original will be affected.

Finally, if you have to do this type of replacement quite a bit, you could use a macro to do it. Here's an example of one:

Sub RemoveSpacesAroundDashes()
    Dim cell As Range
    Dim txt As String

    For Each cell In Selection
        If Not IsEmpty(cell) And VarType(cell.Value) = vbString Then
            txt = cell.Value

            ' Replace non-breaking spaces with regular spaces
            txt = Replace(txt, Chr(160), " ")
            ' Remove duplicate spaces
            txt = WorksheetFunction.Trim(txt)
            ' Remove spaces before and after dashes
            txt = Replace(txt, " - ", "-")
            txt = Replace(txt, " -", "-")
            txt = Replace(txt, "- ", "-")
            cell.Value = txt
        End If
    Next cell
    MsgBox "Spaces around dashes removed.", vbInformation
End Sub

Select the cells you want to affect and then run the macro. It removes non-breaking spaces and duplicate spaces. It then removes the spaces around any dashes.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11779) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.