If cell starts with a blankspace, delete it

%3CLINGO-SUB%20id%3D%22lingo-sub-3484866%22%20slang%3D%22en-US%22%3EIf%20cell%20starts%20with%20a%20blankspace%2C%20delete%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3484866%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20there%20a%20macro%20I%20could%20run%20that%20would%20check%20all%20selected%20cells%20and%20if%20some%20of%20them%20have%20a%20space%20at%20the%20beginning%20of%20them%2C%20it%20would%20delete%20that%20space%20leaving%20just%20the%20rest%20of%20the%20content%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20a%20selected%20cell%20starts%20as%3A%3C%2FP%3E%3CP%3E%26nbsp%3Btesting%3C%2FP%3E%3CP%3E(and%20then%20corrected%20to)%3C%2FP%3E%3CP%3Etesting%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3484866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3484916%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cell%20starts%20with%20a%20blankspace%2C%20delete%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3484916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894985%22%20target%3D%22_blank%22%3E%40mesmrc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20you%20go.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20RemoveLeadingSpaces()%0A%20%20%20%20Dim%20cel%20As%20Range%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20Set%20rng%20%3D%20Selection%0A%20%20%20%20With%20rng%0A%20%20%20%20%20%20%20%20Set%20cel%20%3D%20.Find(What%3A%3D%22%20*%22%2C%20LookIn%3A%3DxlValues%2C%20LookAt%3A%3DxlWhole)%0A%20%20%20%20%20%20%20%20If%20Not%20cel%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Do%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20cel.Value%20%3D%20LTrim(cel.Value)%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Set%20cel%20%3D%20.FindNext(After%3A%3Dcel)%0A%20%20%20%20%20%20%20%20%20%20%20%20Loop%20Until%20cel%20Is%20Nothing%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20With%0A%20%20%20%20Application.ScreenUpdating%20%3D%20True%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3484940%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cell%20starts%20with%20a%20blankspace%2C%20delete%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3484940%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F894985%22%20target%3D%22_blank%22%3E%40mesmrc%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EA%20worksheet%20function%20that%20will%20produce%20a%20clean%20%3CSTRONG%3Ecopy%20%3C%2FSTRONG%3Eof%20the%20data%20is%3CBR%20%2F%3E%3CSTRONG%3E%3D%20TRIM(%3CEM%3Elist%3C%2FEM%3E)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3EVBA%3C%2FSTRONG%3E%20or%20%3CSTRONG%3ETypeScript%3C%2FSTRONG%3E%20is%20needed%20to%20alter%20the%20original.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3493524%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cell%20starts%20with%20a%20blankspace%2C%20delete%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3493524%22%20slang%3D%22en-US%22%3EThanks%20Peter!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3493531%22%20slang%3D%22en-US%22%3ERe%3A%20If%20cell%20starts%20with%20a%20blankspace%2C%20delete%20it%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3493531%22%20slang%3D%22en-US%22%3EYou%20are%20the%20BEST%20in%20the%20biz!%20Thanks%20so%20much%20Hans.%3C%2FLINGO-BODY%3E
Occasional 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.