Welcome back to another #Excel #MacroMonday blog post in my 2019 series. Today let’s look at how to trim the number of characters in a column to say 10. This number is variable and can be specified at the time we wrote the macro. So I find this macro is useful if you regularly have to trim a column of characters, it saves time and prevents having to write a formula every time.
The Manual Formula Method.
Now, if you did want to use formulas the carry out the same process of trimming the data, then LEFT is the one to use. If you need to trim the names of Cities for example in our data set in Column 1, the formula would be as shown below.
The syntax of the LEFT Function is
=LEFT(text,[num_chars])
Where
text is the string that you want to extract the number of characters from
number_of_charcters is an optional argument and is the number of characters you want extract starting at the left-hand side.
Now let’s take a look at how to automate this with an Excel Macro. An easier way to limit column characters in your Excel data.
Preparing To Write The Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook. What’s the difference?. If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks. If you store it in the current workbook then use is restricted to that workbook.
This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.
More About Your Personal Macro Workbook (PMW).
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Starting To Write The Macro.
To start off the macro we need to insert a New Module. Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro. In this example, I have called it simply LimitColumnChar. Notice that Excel will automatically enter the end text End Sub to end the Sub Routine. We simply need to enter the rest of the code between these two lines.
[stextbox id=’info’]
Sub ListMySheets
End Sub
[/stextbox]
Declaring Variables.
We need to declare only one variable in this Macro. Declaring a variable simply means that we create a memory container in Excel to store this value. In this example, we are declaring rng As Range. This is the range of cells that we want Excel to limit the number of charters in. We then set this range as the user-selected range. The user selects the range of cells they want to run this macro on.
[stextbox id=’info’]
Dim rng As Range
Set rng = Selection
[/stextbox]
Use The For Each Loop.
We now use the For Each Loop to loop through all of the cells in the declared range in the above step and limit the characters to 10. A loop allows us to conduct the same kind of activity for many cells or objects in Excel. The syntax is simple.
For Each Object In Collection
Do something
Next Object
This is exactly what we need.
[stextbox id=’info’]
For Each cell In rng
If cell.Value <> “” Then cell.Value = Left(cell.Value, 10)
Next cell
[/stextbox]
Ending The Macro.
The routine then ends with the End Sub piece of code. This was already entered into the module for us when started the type the name of the Macro.
[stextbox id=’info’]
End Sub
[/stextbox]
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below