VB code to remove space from beginning of the text

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

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
You could just use Application.Trim in place of your Remove_Extra_Spaces function. It should improve the speed of the code.
 
Upvote 0

Forum statistics

Threads
1,196,057
Messages
6,013,164
Members
441,751
Latest member
336448

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top