How to Use Truncate in Excel VBA  (3 Effective Ways)

Get FREE Advanced Excel Exercises with Solutions!

Truncate in Excel VBA is a powerful tool that can be used to quickly and efficiently manipulate data in Microsoft Excel. It is used to shorten a number to a specific length. This is often used to make a number easier to read or for calculations. In this article, I will try to explain 3 simple ways on the topic of how to use truncate in Excel VBA. I hope it will be very helpful for you.


Truncate in Excel VBA: 3 Suitable Ways

To use truncate in Excel VBA, I can use a Module or an Immediate Window, or a Microsoft Excel Object. The whole procedures in those three cases are explained in the following section.


1. User Defined Function to Truncate Text

In order to truncate text, we can create a function in VBA and then apply it in Excel.  Let’s follow the steps to learn!

Steps:

  • Create an organized dataset. Here, I have a dataset with football players’ full names. I want the last name from the full names truncating the first portion.

Truncate Text in Excel VBA

  • Next, go to the Developer tab and click on Visual Basic from the ribbon.

  • From the Insert tab, select Module.

Truncate Text in Excel VBA

  • Now, input the following VBA code in that module.
Function TruncatedLastName(str As String, num_chars As Long)
  TruncatedLastName = Right(str, Len(str) - num_chars)
End Function

Excel VBA Truncate

  • Afterward, save the code by pressing  CTRL+S  and the code extension will be  .xlsm .
  • Now, use the following formula with that defined function to truncate a cell.
=TruncatedLastName(C5,7)

Excel VBA Truncate

Read More: How to Truncate Text in Excel


2. Truncate Number with Immediate Window in Excel VBA

The Immediate Window is a command line interface (CLI) that allows developers to quickly and easily interact with the Excel VBA object model. It can be used to run VBA code, evaluate expressions, print out variable values, and debug errors. We can truncate numbers with it too. I have a players’ salary-based dataset where I want to truncate the salary digits after the decimal. In the following section, the process is described in detail.

Steps:

  • First of all, press  ALT+F11  to open the VBA window.
  • Then, click on Immediate Window from the View Alternatively, press  CTRL+G  to open the Immediate Window.

Truncate Number with Immediate Window in Excel VBA

  • Now, write the following code line by line to have the truncated output.
Range("E5") = Format(Range("D5"), "##.##")
Range("E6") = Format(Range("D6"), "##.##")
Range("E7") = Format(Range("D7"), "##.##")
Range("E8") = Format(Range("D8"), "##.##")
Range("E9") = Format(Range("D9"), "##.##")

  • Now, go to the worksheet and we can see our desired output.

Excel VBA Truncate

Read More: How to Truncate Numbers in Excel


3. Truncate Number with VBA Fix Function

There is a function named Fix in VBA that can be used to truncate numbers too. Here, I have Lionel Messi’s weekly salary that I want to truncate. Just follow the steps mentioned below.

Truncate Number with FIX Function

Steps:

  • Firstly, press  ALT+F11  to open the VBA window.
  • Next, input the following code in the Excel Object to truncate the salary.
Option Explicit
Sub TruncatedNumber()
    Range("C6").Value = Fix(Range("C5"))
End Sub

  • Now, click on Run or press  F5  to execute the code. Thus, we will have our desired output.

Excel VBA Truncate


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

At the end of this article, I like to add that I have tried to explain 3 simple ways on the topic of how to use truncate in Excel VBA. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below.


Related Articles


<< Go Back to Excel TRUNC Function | Excel Functions | Learn Excel

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.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo