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

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.

Table of Contents hide

Download Practice Workbook

3 Suitable Ways to Use Truncate in Excel VBA

1. User Defined Function to Truncate Text

2. Truncate Number with Immediate Window in Excel VBA

3. Truncate Number with VBA Fix Function

Conclusion

Download Practice Workbook

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

Truncate with VBA.xlsm

3 Suitable Ways to Use Truncate in Excel VBA

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.

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

From the Insert tab, select Module.

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

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)

Now, use Fill Handle to AutoFill the rest cells in column D.

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.

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.

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 ti truncate. Just follow the steps mentioned below.

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.

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. You can visit our site for more articles about using Excel.