Results 1 to 3 of 3

Thread: VB code to remove space from beginning of the text

  1. #1
    Board Regular
    Join Date
    Jan 2012

    Exclamation VB code to remove space from beginning of the text


    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."
        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, " ")
                Remove_Extra_Spaces = s
            End If
        End With
    End Function

    Srikanth M
    Last edited by RoryA; Mar 28th, 2013 at 09:18 AM. Reason: add code tags

  2. #2
    MrExcel MVP
    RoryA's Avatar
    Join Date
    May 2008

    Default Re: VB code to remove space from beginning of the text

    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.

  3. #3
    Board Regular
    Join Date
    Dec 2011

    Default Re: VB code to remove space from beginning of the text

    You could just use Application.Trim in place of your Remove_Extra_Spaces function. It should improve the speed of the code.
    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts