VBA & Macro Problems

How to Split Text Around Key Terms Using VBA in Excel

Chanuka Geekiyanage Oct 16, 2026 868 views

Question details

The user needs to divide a large dataset into three separate columns: text before a matching key term, the key term itself, and text after the key term, including handling rows with no matching terms automatically.

How to Split Text Around Key Terms Using VBA in Excel

Product: Excel
Device & OS: not provided
Scenario: Parsing and organizing large datasets by extracting specific keywords and separating the surrounding text dynamically without manually updating formulas.
Observed behavior: Currently, separating text based on a dynamic list of key terms requires manual formula editing. An automated VBA solution is needed to cleanly distribute the text and terms into adjacent columns.

Before you start

Before running the macro, ensure you have enabled the Developer tab in your ribbon and saved your workbook as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Use a VBA Procedure to Split Text Around Key Terms

This automated approach loops through your dataset, compares it against a list of key terms, and distributes the separated text into three distinct columns.

Using a VBA macro allows for dynamic text splitting. By utilizing the InStr function, the code can find a keyword's starting position. The Left, Mid, and Right functions are then used to extract the text strings.

Storing key terms in a defined range or dictionary is recommended so you can update your key term list seamlessly without needing to alter the underlying VBA code.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications editor in Excel.

Insert a New Module

Click 'Insert' from the top menu, then select 'Module' to create a blank workspace for your code.

Write the Splitting Code

Create a Sub procedure that loops through your target range. Use a nested loop to check the cell text against an array of key terms using the InStr function. If found, write the text before the term using the Left function, the term itself, and the text after using the Mid function into adjacent offset columns.

Handle Unmatched Rows

Add a conditional statement (If...Then) to output the full original text into the appropriate column if the loop finishes checking the key term array and finds no match.

Run the Macro

Close the VBA editor, select your data range, and press Alt + F8 to select and run your new macro.

Use a VBA Procedure to Split Text Around Key Terms

Dynamic Key Term Range: Keep your key terms in a named range on a separate settings sheet. This way, you can easily add or remove new words without touching the VBA module.

Efficient Data Processing with WPS Office

Automate Text Splitting and Macros with WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, making it incredibly easy to automate repetitive tasks like splitting text around key terms. It offers robust data handling tools and high compatibility with standard Excel macro workbooks.

1. Download and Install WPS Office: Get WPS Office from the official website, install it, and open your workbook using WPS Spreadsheet.
2. Enable the Developer Tab: Navigate to the settings to enable the 'Developer' tab, ensuring macros are enabled for your document.
3. Run Your VBA Code: Click 'VBA Editor' on the ribbon, paste your text-splitting macro into a new module, and execute it just as you would in standard Excel.

Full support for standard VBA and Excel macrosSeamless compatibility with Microsoft Excel .xlsx and .xlsm formatsBuilt-in Text to Columns tool for non-VBA alternativesLightweight, fast, and completely free to download

microsoft office alternative - wps office

Frequently Asked Questions

Can I split text around key terms without VBA using standard Excel formulas?

Yes, you can combine SEARCH, LEFT, MID, and RIGHT functions. However, this becomes very complex and difficult to maintain when checking against a long, dynamic list of multiple key terms. VBA is highly recommended for handling arrays of key terms.

What happens if a cell contains multiple different key terms from the list?

A standard VBA InStr loop will typically match and split based on the first key term it successfully finds in your predefined array. If you need to handle multiple terms, the VBA code would need advanced logic to determine priority or split into even more columns.

Why is my VBA code returning an error on cells with no matching key term?

This usually happens when the code tries to run string manipulation functions (like Left or Mid) with a position value of 0. Ensure your VBA code includes an 'If InStr > 0' check to bypass the splitting functions when a key term is not found.

Does WPS Office support the exact same VBA syntax for string manipulation?

Yes, WPS Spreadsheet supports standard VBA syntax natively. String functions like InStr, Left, Right, and Mid will run flawlessly in WPS Office when working with macro-enabled files.

Excel & Data Problems