How to Use VBA LTrim Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

Excel VBA LTrim function removes leading space from a string. This function is used to develop a Macro to remove leading spaces from a specific text. By the way, the LTrim function cannot remove extra spaces between two texts or from the trailing end. To remove extra spaces between two texts you have to use the VBA Trim function and to remove extra spaces from the end you have to use the VBA RTrim function.

VBA LTrim

This image gives a general overview of the VBA LTrim function. Throughout the article, you will get to know more details and applications of the function.


VBA LTrim Function Overview

Objective

VBA LTrim function removes leading spaces from a string.

❑ Syntax

LTrim(String)

syntax

❑ Argument Explanation

Argument Required/Optional Explanation
String Required The string from which the leading spaces will be removed.

❑ Output

VBA LTrim returns a string.

❑ Version

This function is available from Excel 2000. So any newer version has this function.


1. Removing Leading Spaces in a Message Box

Suppose you want to remove leading spaces from a text string and show it in a message box.

To do that,

➤ Press ALT+ F11.

It will open the VBA window. After that,

➤ Go to the Insert tab and select Module.

It will open the Module(Code) window.

module

➤ Type the following code in the Module(Code) window,

Sub VBA_LTrim()

        MsgBox  LTrim("          Exceldemy to learn Excel")

End Sub

Here, ”          Exceldemy to learn Excel” is the text from which the leading spaces will be removed. The code will remove the leading spaces from the text and will display the result in a message box.

VBA LTrim

➤ Press F5,

As a result, the output of the LTrim function will be displayed in a message box.

VBA LTrim

Read More: How to Use VBA Space Function in Excel (3 Examples)


2. Utilizing VBA LTrim Function to Remove Leading Spaces in the Immediate Window

You can also use the LTrim function to remove leading spaces from a text string and extract the result in the Immediate Window.

➤ Type the following code in the Module(Code) window,

Sub VBA_LTrim()

          Debug.Print LTrim("          Exceldemy to learn Excel")

End Sub

Here, ”          Exceldemy to learn Excel” is the text from which the leading spaces will be removed. The code will remove the leading spaces from the text and will give the result in the Immediate Window.

VBA LTrim

➤ Press CTRL+G.

It will open the Immediate Window. Now,

➤ Press F5

As a result, the output of the LTrim function will be generated in the Immediate Window.

VBA LTrim


Similar Readings


3. Applying VBA LTrim Function to Remove Leading Space from Worksheet

You can use the VBA LTrim function to remove leading spaces from your dataset. Let’s say you have the following dataset where the strings have unnecessary leading spaces.

dataset

➤ Type the following code in the Module(Code) window,

Sub LeadingSpaceRemove()

   Dim Rng As Range
   Dim Selected_Rng As Range
   On Error Resume Next
   VBA_LTrim = "Remove Leading Spaces"
   Set Selected_Rng = Application.Selection
   Set Selected_Rng = Application.InputBox("Range", VBA_LTrim, Selected_Rng.Address, Type:=8)
   For Each Rng In Selected_Rng
   Rng.Value = VBA.LTrim(Rng.Value)
   Next

End Sub

The code will create a Macro which will remove leading spaces from selected cells.

VBA LTrim

After inserting the code,

➤ Close or minimize the VBA window and select the cells from where you want to remove leading spaces in your dataset. Then,

➤ Go to the View tab and click on Macros.

developer

It will open the Macro window.

➤ Select LeadingSpaceRemove from the Macro name box and click on Run.

VBA LTrim

As a result, a new window named Remove Leading Spaces will be popped up showing the selected cell ranges.

➤ Click on OK.

VBA LTrim

As a result, all the leading spaces will be removed from your dataset.

remmoved leading spaces


4. Creating a VBA LTrim Command Button to Remove Leading Spaces

What if you have a switch or button that will allow you to simply click on it to remove all the leading spaces from your selected cells? It will be very convenient and easy to use. Now, I will show you how you can create a button to remove leading spaces using the LTrim function.

If the Developer tab isn’t enabled in your Excel, first you have to enable the Developer tab.


Step-1: Enabling Developer Tab

➤ Go to the File tab and select Options.

options

It will open the Excel Options window.

➤ Go to Customize Ribbon and select Main Tabs from the choose commands from box.

developer

Now,

➤ Select Developer, then click on Add.

At last,

➤ Click on OK.

It will enable the Developer tab in your Excel.

developer


Step-2: Creating a Macro with LTrim

➤ Press ALT+ F11.

It will open the VBA window. After that,

➤ Right-click on the sheet name where you want to create the button and go to Insert > Module.

It will open the Module(Code) window.

VBA LTrim

At this step,

➤ Type the following code in the Module(Code) window,

Sub LTrim_Button()

     Dim Rng As Range
     Dim Selected_Rng As Range
     On Error Resume Next
     Set Selected_Rng = Application.Selection
     For Each Rng In Selected_Rng
     Rng.Value = VBA.LTrim(Rng.Value)
     Next

End Sub

The code will create a Macro named LTrim_Button which will remove the leading spaces from the selected cells.

VBA LTrim


Step-3: Creating the Button

➤ Go to the Developer tab and select Insert > Button (Form Control).

developer

After that,

➤ Drag the mouse to a place on your worksheet where you want to create the button.

After dragging, the Assign Macro window will be opened.

➤ Select LTrim_Button from the Macro name box and click on OK.

VBA LTrim

It will add a button to your worksheet. You can give any name on this button. I have given the name VBA LTrim in this example.

dataset

Now,

➤ Select the cells from where you want to remove leading spaces and click on this button.

As a result, all the leading spaces will be removed with a single click on the button.

leading spaces


💡 Things to Remember When

📌The VBA LTrim function can remove only the leading spaces. If you want to remove Spaces from the middle or from the end you have to use the VBA Trim and the VBA RTrim functions respectively.


📂 Download Practice Workbook


Conclusion

I hope now you know what the VBA LTrim function is and how to apply this function in different ways. If you have any confusion, please feel free to leave a comment.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo