VBA & Macro Problems

Use Excel VBA to Replace Digits in a Four-Digit Number

Nimra Malik Nov 12, 2026 868 views

Question details

Create a VBA macro that replaces every digit in a four-digit value according to a defined digit-to-digit mapping.

Use Excel VBA to Replace Digits in a Four-Digit Number

Product: Microsoft Excel VBA
Device & OS: not provided
Scenario: The user wants to enter a four-digit value, map each character from 0 through 9 to a different digit, and display or store the transformed result.
Observed behavior: The macro must validate that the input contains exactly four numeric characters and preserve leading zeros by handling the value as text.

Before you start

Decide the replacement value for each source digit from 0 through 9 before adding the macro. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm) so the VBA code is retained.

Solution 1Recommended

Create a VBA macro that maps each digit in a four-digit value

Use this method when you want to enter one four-digit value and return its mapped result in a message box.

The macro reads the input as a String rather than a number, so a value such as 0123 keeps its leading zero. Update the digitMap assignments to use the replacements required for your mapping.

Open the Visual Basic Editor

In Excel, press Alt+F11 to open the Visual Basic Editor.

Insert a standard module

Select Insert > Module, then paste the following code into the new module: Sub ReplaceDigits() Dim digitMap(0 To 9) As Integer Dim inputNumber As String Dim outputNumber As String Dim i As Integer digitMap(0) = 2 digitMap(1) = 8 digitMap(2) = 0 digitMap(3) = 1 digitMap(4) = 9 digitMap(5) = 7 digitMap(6) = 4 digitMap(7) = 3 digitMap(8) = 6 digitMap(9) = 5 inputNumber = InputBox("Enter a four-digit number:") If Len(inputNumber) <> 4 Or Not IsNumeric(inputNumber) Then MsgBox "Invalid input. Please enter exactly four digits." Exit Sub End If outputNumber = "" For i = 1 To Len(inputNumber) outputNumber = outputNumber & CStr(digitMap(CInt(Mid(inputNumber, i, 1)))) Next i MsgBox "Mapped number: " & outputNumber End Sub

Change the digit mapping if needed

Edit the lines such as digitMap(0) = 2 and digitMap(1) = 8. The number in parentheses is the original digit, and the value after the equals sign is its replacement.

Run the macro

Press Alt+F8 in Excel, select ReplaceDigits, and choose Run. Enter exactly four digits in the prompt.

Create a VBA macro that maps each digit in a four-digit value

Leading zeros are preserved: Because the input is stored as text, entering 0123 produces a four-character mapped result instead of dropping the first zero.

Solution 2

Adapt the VBA macro to replace digits in worksheet cells

Use this option when values are already stored in a worksheet and you want to write mapped results to another column.

WPS Spreadsheets macro tools

Create a Digit-Replacement Macro in WPS Spreadsheets

WPS Spreadsheets provides a familiar workbook and macro workflow for digit-mapping tasks. You can open a macro-enabled workbook, edit the mapping logic in the macro editor where supported, and save the finished file in a compatible Excel format.

1. Open the workbook in WPS Spreadsheets: Launch WPS Office, choose Spreadsheets, and open the workbook containing the four-digit values or create a new workbook.
2. Prepare the input and output columns: Enter source values in one column and format the destination column as Text when the mapped result can start with zero.
3. Access macro tools: Open the Developer or macro-related tools available in your WPS Spreadsheets version, then create or edit the macro module for the digit mapping.
4. Run and verify the mapping: Run the macro on a test value such as 0123, then confirm that each digit follows your mapping table and the output retains four characters.

Use spreadsheet formulas or macro logic to transform digit strings while preserving leading zeros.Work with Excel macro-enabled workbook formats, including .xlsm files.Use a familiar spreadsheet layout for entering source values and reviewing mapped results.Write transformed values to adjacent cells for batch processing.

microsoft office alternative - wps office

Frequently Asked Questions

Why should the VBA macro store the four-digit number as a String?

A String preserves every entered character, including a leading zero. If VBA treats 0123 as a numeric value, it becomes 123 and no longer has four digits.

How do I change the replacement for a specific digit?

Edit the matching digitMap line in the VBA module. For example, change digitMap(4) = 9 if digit 4 should map to a value other than 9.

Can the macro process more than four digits?

Yes. Remove or change the Len(inputNumber) <> 4 validation condition, then keep the loop based on Len(inputNumber) so it processes every character.

Why does Excel remove the leading zero from my worksheet value?

Excel treats cells formatted as General or Number as numeric values. Format the input and output cells as Text before entering values that can begin with zero.

What file type should I use to keep the VBA code?

Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx removes VBA macros.

Excel & Data Problems