Do you have a column of data in Excel where the spaces at the beginning of each row are causing problems? For example, that space is causing your text to wrap to the next line and making it difficult to read or analyze the data? Yes, that is probably why your lookups are not working. If so, don’t worry, there is a solution. In this blog post we’ll show you how to use a very simple Excel Macro to remove all leading spaces from a user selected range of data in Excel. Let’s get started.
What Does The Macro Do?
Som this simple macro will remove or delete leading spaces in Excel data. The user selected a range of cells, the macro will automatically delete any leading spaces.
How Does The Macro Work?
The Microsoft Excel LTRIM function is used . This removes leading spaces from a string. The syntax of the LTRIM function is :-
LTRIM(text ) where text is the text in Excel you wish to remove the leading spaces from. In this macro Excel loops through the user selected range. Excel removes leading spaces from any text string with extra spaces.
Step 1. Starting The Macro.
First, open Visual Basic, hit ALT +F11 or Developer Tab | Visual Basic | Insert Menu | Module. There are now two options to save the Macro in one of two places.
- Store it in the current Excel workbook you are working on creating.
- Save the Macro in your Personal Macro workbook.
In this instance I can re use the macro over and over again. If I ever need to remove leading spaces in Excel it will be ready. So, by storing it in my Personal Macro Workbook it is available to use whenever the Excel application opens. Insert the module into and name the macro. In this example I have named the macro SubTrimLeadingSpaces.
Step 2. Declaring Variables.
So, the next step is to declare variables. This essentially ensures that a portion of memory is allocated to store these values. In this macro the range of cells the user selects is declared as a variable. The range of cells or Selection of cells is set as the variable range.
Step 3. Use The For Each Loop To Remove Leading Spaces.
Now, the macro really gets to work. The For Each loop is used to loop through each cells i nthe user selected range. Any leading spaces are removed. Once all of the cells in the range are trimmed of any leading spaces the loop ends.
Step 4. Ending The Macro.
Finally the macro ends.. This line of VBA is already in the editor window from the first few steps. Now it is finally time to test the macro.
In this blog post, we showed you how to use a macro in VBA to remove leading spaces from text. This can be a useful tool if you need to clean up data or format text for publication. Therefore, we hope you found this tutorial helpful. Additionally, if you want more Excel tips and tricks, sign up for our newsletter below and we’ll send them straight to your inbox.
More Excel Reading
