Remove spaces and special character?

gameover · May 15, 2009

Hi All,

I have an excel sheet with column 1 containing rows.

It looks like:

Column 1:

Row 1: Very Good
Row 2: Man & dog are friends
Row 3: Good to be home
.
.
.
.
.

I want a macro which removes the spaces between two words and reove the special character also.

The results should look like:

Row 1: Very_Good
Row 2: Man_Dog_are_friends
Row 3: Good_to_be_home

PS: The special character used here is only & and $. But a macro with removing of all special characters would be good.

Please reply in case of questions.

Thanks in advance for your all help!!!!

Domski · May 15, 2009

You can use the SUBSTITUTE function or ASAP Utiliies has an Advanced Character Removal/Replace function which you may find useful. You can download it from here : http://www.asap-utilities.com/

Dom

Sandeep Warrier · May 15, 2009

The code below replaces the special characters with its equivalent english character. You could modify it to suit your needs.

Code:

Sub RemoveSplChar()
Dim rCell As Range

    For Each rCell In ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        With rCell
            .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ð", "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ó", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ó", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ý", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ý", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Þ", "Th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "þ", "th")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Æ", "Ae")
            .Value = Application.WorksheetFunction.Substitute(.Value, "æ", "ae")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ø", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ø", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ä", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ä", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "ü")
            .Value = Application.WorksheetFunction.Substitute(.Value, "À", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "à", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "È", "è")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ì", "ì")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ò", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ù", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ç", "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ç", "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(350), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(351), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Â", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "â", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ê", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ê", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Î", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "î", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ô", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ô", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Û", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "û", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ñ", "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ã", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ã", "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(256), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(257), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(260), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(261), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(258), "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(259), "a")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(264), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(265), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(268), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(269), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(262), "C")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(263), "c")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(270), "D")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(271), "d")
            .Value = Application.WorksheetFunction.Substitute(.Value, "É", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "é", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ë", "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ë", "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(280), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(281), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(274), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(275), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(278), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(279), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(282), "E")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(283), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(284), "G")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(285), "g")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(286), "G")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(287), "g")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(292), "H")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(293), "h")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Í", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "í", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ï", "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ï", "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(302), "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(303), "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(298), "I")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(299), "i")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(308), "J")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(309), "j")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(310), "K")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(311), "k")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(313), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(314), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(315), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(316), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(319), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(320), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(321), "L")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(322), "l")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(323), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(324), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(325), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(326), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(327), "N")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(328), "n")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ö", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ö", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Õ", "O")
            .Value = Application.WorksheetFunction.Substitute(.Value, "õ", "o")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(340), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(341), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(342), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(343), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(344), "R")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(345), "r")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(346), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(347), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(348), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(349), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(350), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(351), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(352), "S")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(353), "s")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(354), "T")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(355), "t")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(356), "T")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(357), "t")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ú", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ú", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ü", "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ü", "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(362), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(363), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(370), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(371), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(366), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(367), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(368), "U")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(369), "u")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ÿ", "Y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ÿ", "y")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ß", "ss")
            .Value = Application.WorksheetFunction.Substitute(.Value, "œ", "oe")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(377), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(378), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(379), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(380), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(381), "Z")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(382), "z")
            .Value = Application.WorksheetFunction.Substitute(.Value, "’", "'")
            .Value = Application.WorksheetFunction.Substitute(.Value, ChrW(1028), "e")
            .Value = Application.WorksheetFunction.Substitute(.Value, ", Ltd", " Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, ", Inc", " Inc")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Ltd", "Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Inc", "Inc.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Inc.orporated", "Incorporated")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Pvt", "Pvt.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "PvtLtd", "Pvt Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, "PLtd", "P. Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "CoPvt", "Co Pvt")
            .Value = Application.WorksheetFunction.Substitute(.Value, " Co ", " Co. ")
            .Value = Application.WorksheetFunction.Substitute(.Value, "CoPvtLtd", "Co Pvt Ltd")
            .Value = Application.WorksheetFunction.Substitute(.Value, "Co Pvt Ltd", "Co. Pvt. Ltd.")
            .Value = Application.WorksheetFunction.Substitute(.Value, "..", ".")
            .Value = Application.WorksheetFunction.Substitute(.Value, ",", ", ")
            .Value = Application.WorksheetFunction.Substitute(.Value, " .", ".")
            .Value = Application.WorksheetFunction.Substitute(.Value, " ,", ",")
            .Value = Application.WorksheetFunction.Substitute(.Value, ". ,", ".,")
            .Value = Application.WorksheetFunction.Substitute(.Value, "  ", " ")
            .Value = Application.WorksheetFunction.Substitute(.Value, "£", "Pounds Sterling")
            .Value = Application.WorksheetFunction.Substitute(.Value, "ƒ", "f")
            .Value = Application.WorksheetFunction.Substitute(.Value, "½", "1/2")
            .Value = Application.WorksheetFunction.Substitute(.Value, "¼", "1/4")
            .Value = Application.WorksheetFunction.Substitute(.Value, "§", "Section")
            
        End With
                
    Next rCell

End Sub

sanrv1f · May 15, 2009

select the range to clean and run this

Code:

Sub clean_1()
Dim cl As Range, i As Integer, st As String, cc As Integer
With Selection
For Each cl In Selection
st = ""
    For i = 1 To Len(cl)
        cc = Asc(LCase(Mid(cl, i, 1)))
        If (cc >= 96 And cc <= 122) Or (cc >= 48 And cc <= 57) Then
            st = st & Mid(cl, i, 1)
        Else
            st = st & "_"
        End If
    Next i
    cl = st
Next cl
Do
    .Replace "__", "_"
Loop While Not (.Find("__") Is Nothing)
End With
End Sub

gameover · May 15, 2009

Thanks Sankar and Sandeep,

Both the codes worked like charm.!!! Thanks again!!!!

Tobiasaw · Jun 24, 2015

Sandeep Warrier said:

The code below replaces the special characters with its equivalent english character. You could modify it to suit your needs.

Code:

Sub RemoveSplChar()
Dim rCell As Range

    For Each rCell In ActiveSheet.Columns("A:A").SpecialCells(xlCellTypeConstants, xlTextValues).Cells
        With rCell
            .Value = Application.WorksheetFunction.Substitute(.Value, "Á", "A")
            .Value = Application.WorksheetFunction.Substitute(.Value, "á", "a")
...
            .Value = Application.WorksheetFunction.Substitute(.Value, "§", "Section")
            
        End With
                
    Next rCell

End Sub

This works very well. I am adapting it to replace more characters than those defined here and it quickly becomes too large to be included in a module. Any suggestions for rewriting it to be smaller and faster with large set of Characters that need to be replaced?

Remove spaces and special character?

gameover

Active Member

Excel Facts

Domski

Well-known Member

Sandeep Warrier

Well-known Member

sanrv1f

MrExcel MVP

gameover

Active Member

Tobiasaw

New Member

Similar threads

Forum statistics

Share this page

Remove spaces and special character?

gameover

Active Member

Excel Facts

Domski

Well-known Member

Sandeep Warrier

Well-known Member

sanrv1f

MrExcel MVP

gameover

Active Member

Tobiasaw

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock