How to Use VBA to Replace Multiple Terms from a Database in Excel
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.

- 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.
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.
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.
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.
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.
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.
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.
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. Open your file in WPS Spreadsheet: Launch WPS Office and open your .xlsm workbook containing the data you want to process.
- 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. Run your VBA scripts: Click the Visual Basic Editor button to paste, edit, and execute your custom replacement scripts effortlessly.

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.




