If cell starts with a blankspace, delete it

Copper Contributor

Is there a macro I could run that would check all selected cells and if some of them have a space at the beginning of them, it would delete that space leaving just the rest of the content?

 

For example, a selected cell starts as:

 testing

(and then corrected to)

testing

4 Replies

@mesmrc 

Here you go.

Sub RemoveLeadingSpaces()
    Dim cel As Range
    Dim rng As Range
    Application.ScreenUpdating = False
    Set rng = Selection
    With rng
        Set cel = .Find(What:=" *", LookIn:=xlValues, LookAt:=xlWhole)
        If Not cel Is Nothing Then
            Do
                cel.Value = LTrim(cel.Value)
                Set cel = .FindNext(After:=cel)
            Loop Until cel Is Nothing
        End If
    End With
    Application.ScreenUpdating = True
End Sub

@mesmrc 

A worksheet function that will produce a clean copy of the data is
= TRIM(list)

VBA or TypeScript is needed to alter the original.

Thanks Peter!!
You are the BEST in the biz! Thanks so much Hans.