hellothere4
New Member
- Joined
- May 9, 2011
- Messages
- 17
Hello, I'm just looking for a simple way to delete all characters that aren't of the standard type, like a-z, 0-9, .'!? THANKS!!
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to delete everything but "normal" characters
- Thread starter hellothere4
- Start date
Sektor
Well-known Member
- Joined
- May 6, 2011
- Messages
- 2,929
- Office Version
- 365
- Platform
- Windows
Code:
Function ClearString(ByVal sWord As String) As String
Dim i As Integer
Dim sNewWord As String, sCharacter As String, sPattern As String
Dim re As RegExp, m As Match
sWord = "BCDa%"
sPattern = "[a-z0-9.'!?]"
Set re = New RegExp
With re
.IgnoreCase = True
.Pattern = sPattern
End With
For i = 1 To Len(sWord)
sCharacter = Mid$(sWord, i, 1)
If re.Test(sCharacter) Then
sNewWord = sNewWord & sCharacter
End If
Next
ClearString = sNewWord
End Sub
Upvote
0
Hi
If Clean() does not solve your problem then I think a udf approach like Sektor suggests will.
I think that in this case you don't need to both loop through the characters and use Regex's.
2 options.
1 - Use a RegEx, for ex.:
2 - loop through the characters:
If Clean() does not solve your problem then I think a udf approach like Sektor suggests will.
I think that in this case you don't need to both loop through the characters and use Regex's.
2 options.
1 - Use a RegEx, for ex.:
Code:
Function ClearString1(ByVal sWord As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^a-zA-Z0-9.'!?]"
ClearString1 = .Replace(sWord, "")
End With
End Function2 - loop through the characters:
Code:
Function ClearString2(ByVal sWord As String) As String
Dim i As Integer
Dim sNewWord As String, sCharacter As String, sPattern As String
sPattern = "[a-zA-Z0-9.'!?]"
For i = 1 To Len(sWord)
sCharacter = Mid$(sWord, i, 1)
If sCharacter Like sPattern Then
sNewWord = sNewWord & sCharacter
End If
Next
ClearString2 = sNewWord
End Function
Upvote
0
I've used a different approach to the regex to remove all the non-confomring caharacters in one shot
If you add this VBA function to your workbook then you can then call it either as a
a) worksheet function
for example
=RegexRemove(A1)
in cell B1 to remove any non a-z0-9!.' from A1
b) via other code
hth
Dave
If you add this VBA function to your workbook then you can then call it either as a
a) worksheet function
for example
=RegexRemove(A1)
in cell B1 to remove any non a-z0-9!.' from A1
b) via other code
hth
Dave
Code:
Function RegexRemove(strIn As String)
Dim Regex
Set Regex = CreateObject("vbscript.regexp")
With Regex
.IgnoreCase = True
.Global = True
.Pattern = "[^\w+\s\.\!']"
RegexRemove = .Replace(strIn, vbNullString)
End With
End Function
Upvote
0
Hi
In my previous post I forgot to set the Global property of the RegEx:
In my previous post I forgot to set the Global property of the RegEx:
Code:
Function ClearString1(ByVal sWord As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "[^a-zA-Z0-9.'!?]"
[COLOR=black] .Global = True
[/COLOR] ClearString1 = .Replace(sWord, "")
End With
End Function
Upvote
0
Upvote
0
Similar threads
- Question
- Solved
