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

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.


📂 Download Practice Workbook


Introduction to the LTrim Function

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.


4 Examples of Using VBA LTrim Function in Excel

1. Remove 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


2. VBA LTrim 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


3. VBA LTrim 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 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. Create a VBA LTrim Command Button to Remove Leading Spaces

What if you have a switch or button which 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.


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


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


4.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 in 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 your 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.


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.

Prantick

Prantick

Hi, I'm Prantick Bala, currently working and doing research on Microsoft Excel in Softeko.com. I have completed BSc in Naval Architecture and Marine Engineering From Bangladesh University of Engineering and Technology (BUET). Currently I am in the last semester of my MBA at Institute of Business Administration (IBA), Dhaka University (DU). I'm always eager to learn through experience and have a deep interest in solving problem to get optimum solution.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo