sshrikanth2
Board Regular
- Joined
- Jan 17, 2012
- Messages
- 138
Hi,
I need your help...
I have a VB code which removes the special characters from the text and replace the same with space and if there are more than one space, it will make them one.
But, my problem here is a space will appear when there is a special character in the beginning of the text and it should not appear in the beginning of the text. I appreciate your help.
Following is the code that i am using currently...
Regards,
Srikanth M
I need your help...
I have a VB code which removes the special characters from the text and replace the same with space and if there are more than one space, it will make them one.
But, my problem here is a space will appear when there is a special character in the beginning of the text and it should not appear in the beginning of the text. I appreciate your help.
Following is the code that i am using currently...
Code:
Sub TIN_TEXT()
Dim myString As String, ce As Range, i As Long, re As Object
Set re = CreateObject("VBScript.RegExp")
Application.ScreenUpdating = False
For Each ce In Range("A4:B1048576")
For i = Len(ce.Value) To 1 Step -1
ce.Value = Replace(ce.Value, "&", " AND ")
ce.Value = Remove_Extra_Spaces(ce.Value, re) '//Warning: will return string values (even numeric values are returned as text).
Select Case Mid(ce.Value, i, 1)
Case Is = "`", "!", "@", "#", "$", "%", ";", "^", "(", ")", "_", "-", "=", "+", _
"{", "[", "}", "]", "\", "|", ";", ":", "'", """", ",", "<", ".", ">", "/", "?", "*", "®"
myString = Replace(ce.Value, Mid(ce.Value, i, 1), "")
ce.Value = myString
End Select
Next i
myString = ""
Next ce
Application.ScreenUpdating = True
If i = 1 Then
MsgBox "Sorry! No Special Characters."
Else
MsgBox "All special characters removed!", vbOKOnly
End If
End Sub
Private Function Remove_Extra_Spaces(ByVal arg, ByRef re As Object) As String
'//Replace two or more spaces with a single space
Dim s As String
s = CStr(arg)
With re
.Pattern = "[ ]{2,}"
.Global = True
If .Test(s) Then
Remove_Extra_Spaces = .Replace(s, " ")
Else
Remove_Extra_Spaces = s
End If
End With
End Function
Regards,
Srikanth M
Last edited by a moderator: