VBA & Macro Problems

How to Use VBA to Replace Multiple Terms from a Database in Excel

Maira MehtabMaira Mehtab Nov 10, 2026 868 views

Question details

The user needs a way to replace multiple abbreviated or misspelled terms in a cell by looking them up against a reference database column using VBA.

How to Use VBA to Replace Multiple Terms from a Database
Product
Excel
Device & OS
not provided
Scenario
Standardizing semicolon-separated text strings by cross-referencing abbreviations with a master database column to find the correct terms.
Observed behavior
Semicolon-separated abbreviations are matched against database entries, substituting correct terms into a new output column while leaving unmatched text unchanged.
Before you start

Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) before running the code. It is also recommended to create a backup of your data sheet to prevent accidental overwriting.

Solution 1Recommended

Use a Custom VBA Macro for Database Replacements

Write a VBA script that splits the target cell contents, cross-references a database column, and outputs the corrected text.

This solution uses the VBA Split function to isolate individual terms separated by semicolons. It then iterates through a database in Column A, using a Select Case statement to handle specific abbreviations like 's.' for strawberry or 'l.' for light blue, and outputs the corrected text into Column D.

1
Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor, then click Insert > Module from the top menu to create a new module.

2
Paste the Macro Code

Copy and paste your database replacement VBA code into the blank module window. Ensure your code includes the 'Split' logic for "; " and the 'Select Case' logic for your specific abbreviations.

3
Adjust the Cell Ranges

Modify the column references in the code to match your worksheet layout. For example, ensure the database loop targets Column A, the source data targets Column C, and the output targets Column D.

4
Run the Macro

Press F5 or click the Run button in the toolbar to execute the macro. The script will process the rows and populate the corrected terms in your target output column.

Macro Execution: The macro is designed to preserve the original text in the output column if no matching replacement is found in your database column.
Efficient Data Processing with WPS Spreadsheet

Run VBA Macros Seamlessly in WPS Office

WPS Spreadsheet fully supports VBA and macros, allowing you to automate repetitive tasks like bulk text replacement just as you would in Microsoft Excel. It offers a familiar interface and robust macro tools.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm workbook containing the data you want to process.
  2. 2. Enable the Developer tools: Navigate to the Tools tab on the top ribbon and click Developer to access the Macro and VBA Editor features.
  3. 3. Run your VBA scripts: Click the Visual Basic Editor button to paste, edit, and execute your custom replacement scripts effortlessly.
Full support for VBA macros, scripts, and advanced formulas.100% compatibility with Microsoft Excel (.xlsm) macro-enabled files.Lightweight software that runs smoothly and processes large datasets quickly.
microsoft office alternative - wps office

Frequently Asked Questions

How do I add more specific abbreviations to the VBA code?

You can extend the `Select Case` statement in the VBA code. Simply add a new `Case Is = "your_abbreviation"` block and define the corresponding lookup logic and substitution parameters for the new term.

Why are my original texts not changing after running the macro?

This usually happens if the source text formatting doesn't exactly match the delimiter used in the code (such as missing spaces after the semicolons), or if the database column does not contain the exact target words for the script to match.

Can I output the replaced terms to the same column instead of a new one?

Yes, you can modify the code to overwrite the original column by changing the output destination in the VBA script (for example, changing `Cells(i, 4).Value` to `Cells(i, 3).Value`). However, keeping a separate output column initially is recommended for data verification.

Does this macro handle case sensitivity?

By default, the `Like` operator and text comparisons in VBA are case-sensitive. You can add `Option Compare Text` at the very top of your module before the `Sub` declaration to make all string comparisons in that module case-insensitive.