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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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!
- 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.
- Now, use Fill Handle to AutoFill the rest cells in column D.
Read More: How to Truncate Text from Right in Excel (5 Easy Methods)
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.
- 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.
Read More: How to Truncate Decimal in Excel (6 Easy Ways)
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.
- 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.
Read More: How to Truncate Date in Excel (4 Simple Methods)
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.