Avatar of finnstone
finnstone

asked on 

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
VBA

Avatar of undefined
Last Comment
finnstone
SOLUTION
Avatar of Bill Prew
Bill Prew

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of finnstone
finnstone

ASKER

thanks Bill. I have 40k rows. It says no repsonding. I will check it in the morning but i dont tihnk its running well
Avatar of Bill Prew
Bill Prew

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
SOLUTION
Avatar of Bill Prew
Bill Prew

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of finnstone
finnstone

ASKER

would that be line 10 and 11 bill?
Avatar of finnstone
finnstone

ASKER

wow regex worked in like 1 second
VBA
VBA

Visual Basic for Applications (VBA) enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library, but it can normally only run code within a host application rather than as a standalone program. It can, however, be used to control one application from another via OLE Automation. VBA is built into most Microsoft Office applications.

17K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo