none
Delete characters in a cell RRS feed

  • Question

  • I want to remove the following characters from all of the cells in column "A":

    - "*"

    - "@"

    - "#"

    After they are removed, I want to trim all of the cells in column "A"

    Thank you


    Tuesday, April 2, 2013 3:27 PM

Answers

  • You can use this macro:

    Sub DeleteSomeChars()
        Dim cel As Range
        Application.ScreenUpdating = False
        With Range("A:A")
            .Replace What:="~*", Replacement:="", LookAt:=xlPart
            .Replace What:="@", Replacement:="", LookAt:=xlPart
            .Replace What:="#", Replacement:="", LookAt:=xlPart
            For Each cel In .SpecialCells(xlCellTypeConstants)
                cel.Value = Trim(cel.Value)
            Next cel
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Tuesday, April 2, 2013 4:34 PM

All replies

  • You can use this macro:

    Sub DeleteSomeChars()
        Dim cel As Range
        Application.ScreenUpdating = False
        With Range("A:A")
            .Replace What:="~*", Replacement:="", LookAt:=xlPart
            .Replace What:="@", Replacement:="", LookAt:=xlPart
            .Replace What:="#", Replacement:="", LookAt:=xlPart
            For Each cel In .SpecialCells(xlCellTypeConstants)
                cel.Value = Trim(cel.Value)
            Next cel
        End With
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Tuesday, April 2, 2013 4:34 PM
  • Thank you. You're the man!!!
    Tuesday, April 2, 2013 4:52 PM