remove all special characetrs, except for space

this code removes special characeters but it also removes spaces and i need to keep spaces. any help aprpeciated!

Sub DeleteNonAlphaNumericChars()
Dim Constants As Range
Dim Char As String
Dim Txt As String
Dim Cell As Range
Dim i As Integer
' Change sheet name as needed:
Const SheetName = "Sheet1"
Set Constants = ThisWorkbook.Sheets( _
SheetName).Cells.SpecialCells( _
xlCellTypeConstants)
For Each Cell In Constants
Txt = vbNullString
For i = 1 To Len(Cell.Text)
Char = Mid(Cell.Text, i, 1)
Select Case Asc(Char)
Case Asc("0") To Asc("9"):
Txt = Txt & Char
Case Asc("a") To Asc("z"):
Txt = Txt & Char
Case Asc("A") To Asc("Z"):
Txt = Txt & Char
End Select
Next i
Cell.Value = Txt
Next Cell
End Sub
finnstoneAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
Try this:

Sub DeleteNonAlphaNumericChars()
    Dim Constants As Range
    Dim Char As String
    Dim Txt As String
    Dim Cell As Range
    Dim i As Integer
    ' Change sheet name as needed:
    Const SheetName = "Sheet1"
    Set Constants = ThisWorkbook.Sheets( _
    SheetName).Cells.SpecialCells( _
    xlCellTypeConstants)
    For Each Cell In Constants
        Txt = vbNullString
        For i = 1 To Len(Cell.Text)
            Char = Mid(Cell.Text, i, 1)
            Select Case Asc(Char)
                Case Asc(" "):
                    Txt = Txt & Char
                Case Asc("0") To Asc("9"):
                    Txt = Txt & Char
                Case Asc("a") To Asc("z"):
                    Txt = Txt & Char
                Case Asc("A") To Asc("Z"):
                    Txt = Txt & Char
            End Select
        Next i
        Cell.Value = Txt
    Next Cell
End Sub

Open in new window


»bp
Fabrice LambertConsultingCommented:
Hi,

It will be shorter and easyer to read with a regex:
Sub DeleteNonAlphaNumericChars()
    Dim Constants As Range
    Dim Cell As Range
    Dim rx As Object

        ' Change sheet name as needed:
    Const SheetName = "Sheet1"
    Set Constants = ThisWorkbook.Sheets(SheetName).Cells.SpecialCells(xlCellTypeConstants)
    Set rx = CreateObject("VBScript.RegExp")
    rx.Pattern = "[^0-9A-Za-z ]"
    For Each Cell In Constants
        Cell.value = rx.Replace(Cell.Value, vbNullString)
    Next Cell
    Set rx = Nothing
    Set Constants  = Nothing
End Sub

Open in new window

Note: The regex pattern can also be "[^\d\w  ]"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
thanks Bill. I have 40k rows. It says no repsonding. I will check it in the morning but i dont tihnk its running well
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Bill PrewIT / Software Engineering ConsultantCommented:
Yes, the original approach was pretty heavy in processing, so adding another condition would only slow it down further.

Have you tried the REGEX approach?


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
You will need to add the  second line shown below to the REGEX approach.  In tests here it runs way faster than the character by character approach, go with it.

    rx.Pattern = "[^0-9A-Za-z ]"
    rx.Global = True

Open in new window


»bp
finnstoneAuthor Commented:
would that be line 10 and 11 bill?
finnstoneAuthor Commented:
wow regex worked in like 1 second
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.