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

Method 1 – User-Defined Function to Truncate Text


  • 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.

Truncate Text in Excel VBA

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

  • From the Insert tab, select Module.

Truncate Text in Excel VBA

  • 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

Excel VBA Truncate

  • 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.

Excel VBA Truncate

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.


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

Truncate Number with Immediate Window in Excel VBA

  • 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.

Excel VBA Truncate

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.

Truncate Number with FIX Function


  • 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.

Excel VBA Truncate

Download the Practice Workbook

Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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