VB code to remove space from beginning of the text

[sshrikanth2]

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...

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

 

[RoryA]

You can use Trim to remove spaces from the start and end of the word, or LTrim if you only want to remove them from the start.

 

[Firefly2012]

You could just use Application.Trim in place of your Remove_Extra_Spaces function. It should improve the speed of the code.