EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA String and Text Functions VBA TRIM
Secondary Sidebar
VBA String and Text Functions
  • VBA String and Text
    • VBA Constants
    • VBA TRIM
    • VBA Find
    • VBA Case
    • VBA Replace
    • VBA String
    • VBA Split
    • VBA InStr
    • VBA RegEx
    • VBA Left
    • VBA UCASE
    • VBA LCase
    • VBA Concatenate
    • VBA StrComp
    • VBA StrConv
    • VBA Find and Replace
    • VBA Right
    • VBA ASC
    • VBA InStrRev
    • VBA Length of String
    • VBA Format
    • VBA MID
    • VBA Concatenate Strings
    • VBA SubString
    • VBA String Comparison
    • VBA String Array
    • VBA Dynamic Array
    • VBA Replace String

VBA TRIM

By Ashwani JaiswalAshwani Jaiswal

VBA TRIM FUNCTION

Excel VBA Trim Function

Excel VBA Trim Function is used for removing the extra spaces from any cell or text and gives us the output which has a standard in terms of required spaces. VBA Trim function works exactly as the Excel Trim function and Trim function also removes the extra spaces in 3 ways;

  1. Spaces from the starting of the text.
  2. Spaces from the end of the text.
  3. Spaces from the middle of the text if more than 1 extra space is seen.

This mostly happens when we download data from some server, data warehouse or while incorrectly inserting the extra spaces manually. Even though we can easily see the spaces at the middle of the text, but spaces at the beginning and end of text cannot be seen easily until and unless we go to edit mode of that specific cell. This can be done by VBA Marco.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Use Excel VBA Trim Function?

We will discuss how to use VBA Trim Function by using some examples.

You can download this VBA Trim Function Excel Template here – VBA Trim Function Excel Template

VBA Trim Function – Example #1

Here we have 3 cells in the below screenshot.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

Excel Data

And each cell has some spaces associated with them. Cell A1 has spaces at the end.

Cell A1

Cell A2 has spaces at the beginning of the text.

Cell A2

And cell A3 has space in between the text which is highlighted in the below screenshot.

Cell A3

For trimming these cells we will use Excel VBA. For that, press Alt + F11 keys together or under Developer tab click on Visual Basic as shown below.

VBA Option

This will take us to Visual Basic coding window. Now go to the Insert menu from VBA window and select Module as shown below.

Insert Module

This will create a new Module where we will write a code for trimming. Below are the steps for creating and writing trimming code:

  • First, choose a variable and define a range. Here we have selected the variable “A”. This variable can be anything.
  • As our data set has already some text or object then we need to set the range for it. And for that, we will use “Selection”. It will automatically select the data in the present sheet.

Code:

Sub Trim_Data()
   
    Dim A As Range
    
    Set A = Selection

End Sub

Selection

  • For selecting each filled cell of the sheet “For Each” is the function And Value function is used for selecting the value with Trim(Cell) selection.

Code:

Sub Trim_Data()

    Dim A As Range

    Set A = Selection

    For Each cell In A
    cell.Value = WorksheetFunction.Trim(cell)
    Next

End Sub

Trim Function

By this, we complete the coding work for creating macro through VBA Trim. Now let’s apply the created code into some tab. For this go to Insert Menu and under Illustrations select Shapes.

Shapes Option

From that Shapes option, create any shape using shape formats and styles.

Shape Formats

Here we have created a rectangular box and named it TRIM as shown below.

Trim Box

Now click on the Design Mode in Developer menu and right-click on the created box or shape. From the right-click, menu list select Assign Macro to link our created code as shown below.

Assign Macro

We will get the Assign Marco window, from there select already created macro code, here we have Trim_Data and then click on OK.

How to Assign Macro

Now select the data and click on the TRIM button. We will get the selected trimmed and spaces that were not required are now removed as shown below.

 Result after Clicking on Trim Button

VBA Trim Function – Example #2

VBA Coding shown above can be written in one more way. In the above example, we only trimmed the selected data. In this we will insert the function in the message box as well it will show once the data is trimmed.

For this, we will use the same data which we used in example 1. Follow the same steps, go to the Developer tab and click on Visual Basic.

VBA Option

Once we do that, we will get the Visual Basic window. Now open a fresh Module and start coding on that page.

Now consider the same code which we have written in example-1. But here for printing a message box with the text we will insert some text.

Code:

Sub Trim_Data2()

    Dim A As Range

    Set A = Selection

    For Each cell In A
    cell.Value = WorksheetFunction.Trim(cell)

    Dim B As String

    B = Trim("Trimming Done")

    MsgBox B

    Next

End Sub

Trimming Function Message

Once we are done with writing the code, close the Visual Basic windows.

Now go to the Developer tab again and click on Insert menu as shown below. We will get a drop-down menu of different button shapes as shown below.

Insert Button

Now select any of the button shapes and draw it on anywhere on your workbook. From the list of created macros select the required option. Here we have selected Trim_Data2 which we created for this example and then click OK.

Assign Macro to Button

After that, we will get the button created on the screen.

Assign Macro to Renamed Button

We can name the button as per function which we will be performing. Let’s name it TRIM as per function. Now right click on the created TRIM button and select Assign Marco. Once we do that we will get the window Assign Marco as shown below. Now select the required macro and click OK.

Assign Macro to Button

By this, our trim coding will get assigned to TRIM Button. Now for testing select the data first and then click on this TRIM Button.

Trim Message Box

As we can see above, here our data got trimmed and we can see the message as well of “Trimming Done”.

Pros of Excel VBA Trim Function

  • We can trim huge sets of data in one shot without checking the number of extra spaces the cells have.
  • Coding of trimming function is also quite small.
  • There are very less or no chances that any extra space will get spared.

Things To Remember

  • Always save the file as Macro-Enabled Worksheet. By doing this, we can use assigned macro multiple time.
  • Compile the code before assigning it to any button.
  • There are 2 different ways are shown for creating a button and both can be used as per individuals need or wish.
  • Consider writing VBA Code as shown in example-2 which will give the message as well.

Recommended Articles

This has been a guide to Excel VBA Trim. Here we discussed how to use VBA Trim Function to remove spaces in Excel along with some practical examples and downloadable excel template. You can also go through our other suggested articles–

  1. VBA Function
  2. VBA VLOOKUP
  3. VBA Get Cell Value
  4. VBA XML
3 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more