sshrikanth2
Board Regular
- Joined
- Jan 17, 2012
- Messages
- 138
Hi,
I need you help....
The following code will remove the special characters from a text line but i need a coding line to be added to this which replace special character "&" with "AND". Also, if there are more than two spaces in a text it should be condensed to only one.
Any help will be appreciated.
Private Sub CommandButton1_Click()
Dim myString As String, ce As Range, i As Long
Application.ScreenUpdating = False
For Each ce In Range("A3:A500")
For i = Len(ce.Value) To 1 Step -1
Select Case Mid(ce.Value, i, 1)
Case Is = "`", "!", "@", "#", "$", ";", "^", "(", ")", "_", "-", "=", "+", _
"{", "[", "}", "]", "\", "|", ";", ":", "'", """", ",", "<", ".", ">", "/", "?"
myString = Replace(ce.Value, Mid(ce.Value, i, 1), "")
ce.Value = myString
' Do Nothing
End Select
Next i
myString = ""
Next ce
Application.ScreenUpdating = True
End Sub
Regards,
Srikanth M
I need you help....
The following code will remove the special characters from a text line but i need a coding line to be added to this which replace special character "&" with "AND". Also, if there are more than two spaces in a text it should be condensed to only one.
Any help will be appreciated.
Private Sub CommandButton1_Click()
Dim myString As String, ce As Range, i As Long
Application.ScreenUpdating = False
For Each ce In Range("A3:A500")
For i = Len(ce.Value) To 1 Step -1
Select Case Mid(ce.Value, i, 1)
Case Is = "`", "!", "@", "#", "$", ";", "^", "(", ")", "_", "-", "=", "+", _
"{", "[", "}", "]", "\", "|", ";", ":", "'", """", ",", "<", ".", ">", "/", "?"
myString = Replace(ce.Value, Mid(ce.Value, i, 1), "")
ce.Value = myString
' Do Nothing
End Select
Next i
myString = ""
Next ce
Application.ScreenUpdating = True
End Sub
Regards,
Srikanth M