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

Method 1 – User-Defined Function to Truncate Text

Steps:

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

• Go to the Developer tab and click on Visual Basic.

• From the Insert tab, select Module.

• Input the following VBA code in the module.

Function TruncatedLastName(str As String, num_chars As Long)
  TruncatedLastName = Right(str, Len(str) - num_chars)
End Function

• Save the code by pressing Ctrl + S and use the file extension .xlsm.
• Use the following formula with the defined function to truncate a cell.

• Use the Fill Handle to autofill the cells in column D.

Read More: How to Truncate Text in Excel

Method 2 – Truncate a Number with the 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 have a salary dataset where we want to truncate the salary digits after the decimal.

Steps:

• Press Alt + F11 to open the VBA window.
• Click on Immediate Window from View. Alternatively, press CTRL + G to open the Immediate Window.

• Insert the following code lines to get 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"), "##.##")

• Go to the worksheet.

Read More: How to Truncate Numbers in Excel

Method 3 – Truncate the Number with a VBA Fix Function

There is a function named Fix in VBA that can be used to truncate numbers. We have Lionel Messi’s weekly salary that we want to truncate.

Steps:

• Press Alt + F11 to open the VBA window.
• Input the following code in an Excel Object to truncate the salary.

Option Explicit
Sub TruncatedNumber()
    Range("C6").Value = Fix(Range("C5"))
End Sub

• Click on Run or press F5 to execute the code.

Download the Practice Workbook

Related Articles

• How to Truncate Text from Right in Excel
• How to Truncate Text from Left in Excel
• How to Truncate Date in Excel
• How to Stop Excel from Truncating Text

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