Remove spaces and special character?

G

gameover

Active Member
Joined
Jan 12, 2009
Messages
292
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!!!!
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
Click to expand...

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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
How to remove numbers but only if immediately after a non numeric character
2 3
Replies
20
Views
526
hagia_sofia
hagia_sofia
VinceF
  • Solved
Insert Spaces in SaveAs Filename
Replies
5
Views
83
mumps
mumps
R
  • Solved
Using "Special Characters" in VBA
Replies
4
Views
345
Alex Blakenburg
A
Dan Wilson
  • Solved
Format help
Replies
7
Views
145
Alex Blakenburg
A
zookeepertx
  • Solved
Trying (unsuccessfully) to pull a date from the middle of a string
2 3
Replies
21
Views
326
zookeepertx
zookeepertx

Forum statistics

Threads
1,223,723
Messages
6,174,123
Members
452,546
Latest member
Rafafa

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top