troubleshooting Question

remove all special characetrs, except for space

Avatar of finnstone
finnstone asked on
VBA
7 Comments3 Solutions81 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 3 Answers and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros