VBA & Macro Problems

How to Use an Excel Mapping Table to Find and Replace Text with VBA

Chanuka Geekiyanage Oct 8, 2026 869 views

Question details

The user needs to find and replace multiple values in a target column based on a two-column mapping table, while avoiding incorrect partial replacements of overlapping strings.

How to Use an Excel Mapping Table to Find and Replace Text using VBA

Product: Excel
Device & OS: not provided
Scenario: Replacing specific text identifiers (e.g., VP1, VP3, VP30) in a column using an external lookup or mapping table, with the final results output to a new column.
Observed behavior: Standard find and replace methods or basic formulas may incorrectly replace overlapping strings (e.g., replacing 'VP3' inside 'VP30'). A structured VBA function is required to process the mapping range accurately.

Before you start

Ensure your two-column mapping table is sorted by the search criteria column in descending order of string length, so longer strings like 'VP30' are processed before shorter overlapping strings like 'VP3'.

Solution 1Recommended

Use a Custom VBA Function (ReplaceFromTable)

Create a User-Defined Function (UDF) in VBA to loop through your two-column mapping table and safely replace multiple values in your target column.

When performing bulk text replacements, processing order is critical. If your mapping table contains overlapping text, replacing a shorter text string first will corrupt longer strings containing that text. Sorting your search criteria and utilizing a custom VBA loop ensures each replacement is executed properly.

Sort the Mapping Table

Select your mapping table range (e.g., B2:C34). Sort the primary column containing your original values in descending order of length to prioritize longer strings (like VP30) over shorter ones (like VP3).

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in Excel.

Insert a New Module

Click 'Insert' > 'Module' from the top menu, then paste your custom ReplaceFromTable VBA script into the blank coding window.

Apply the Function

Return to your worksheet. Click on your output cell (e.g., G2), and enter the formula =ReplaceFromTable(E2, $B$2:$C$34). Drag the fill handle down to apply it to the rest of the column.

Use a Custom VBA Function (ReplaceFromTable)

Save as Macro-Enabled: Remember to save your workbook as an Excel Macro-Enabled Workbook (.xlsm) to ensure your custom function is preserved.

Easily Manage VBA Macros with WPS Spreadsheet

WPS Spreadsheet provides robust, native support for VBA macros, enabling you to effortlessly execute custom find-and-replace functions using mapping tables without complex workarounds.

1. Download and Install WPS Office: Download WPS Office for free and open your existing Excel workbook (.xlsx or .xlsm) in WPS Spreadsheet.
2. Access the Developer Tools: Navigate to the 'Tools' or 'Developer' tab in the top ribbon to access your macro and security settings.
3. Run Your VBA Code: Click 'Visual Basic' (or press Alt+F11) to paste your mapping table script, and apply your custom function directly in your worksheet.

Native support for VBA macros and User-Defined Functions (UDFs)100% format compatibility with Microsoft Excel (.xlsx and .xlsm) filesClean, intuitive Developer tab for quick access to the Visual Basic Editor

microsoft office alternative - wps office

Frequently Asked Questions

Why is my find and replace altering parts of longer text strings?

This happens due to overlapping text. If your mapping table contains 'VP3' and 'VP30', replacing 'VP3' first will incorrectly change the beginning of 'VP30'. Always sort your mapping table to process longer values first.

Do I need to enable macros to use this custom replacement function?

Yes, because the solution uses a custom VBA function (=ReplaceFromTable), you must enable macros in your Trust Center or Macro Security settings for the formula to calculate properly.

Can I use standard Excel formulas instead of VBA for mapping tables?

While nested SUBSTITUTE functions can work for replacing a small handful of items, they become impossibly complex for a 30+ item mapping table. VBA is the cleanest and most reliable method for looping through large replacement tables.

Excel & Data Problems