VBA & Macro Problems

How to Parse Delimited Values and Create Column Groups using Excel VBA

Algirdas Jasaitis Sep 28, 2026 869 views

Question details

The user needs a method to split semicolon-delimited values, organize them into dictionary keys, and generate dynamically grouped columns with headers while keeping the original worksheet data intact.

How to Parse Delimited Values and Create Column Groups using Excel VBA

Product: Excel
Device & OS: not provided
Scenario: Parsing complex delimited text strings into logically grouped and categorized columns for advanced data analysis.
Observed behavior: The goal is to automatically extract delimited elements into an organized array and worksheet layout without overwriting the source dataset.

Before you start

Ensure the Developer tab is enabled in your ribbon and verify that your source data contains consistent semicolon delimiters to prevent array index out-of-bounds errors during the script execution.

Solution 1Recommended

Normalize Data and Reshape with a PivotTable

Instead of relying on a complex VBA script to force a pivoted layout, it is highly recommended to normalize your data into separate columns and use a PivotTable. This approach is much easier to maintain.

A rigid pivoted layout created by VBA can break easily if new data attributes are introduced. By structuring the parsed data into distinct columns (like size, prefix, and value), you leverage built-in Excel tools to dynamically group and display the information.

Split the Delimited Data

Select the column containing your semicolon-delimited text. Go to the Data tab and click 'Text to Columns'. Choose 'Delimited', select 'Semicolon', and finish the wizard to separate the values.

Structure the Normalized Table

Organize the split data into a clear tabular format with dedicated header columns for size, prefix, value, and any additional attributes.

Insert a PivotTable

Highlight your newly structured table, navigate to the Insert tab, and click 'PivotTable'. Choose a destination on a new worksheet to preserve your original data.

Configure the Pivot Layout

In the PivotTable Fields pane, drag the 'size' and 'prefix' fields into the Columns area, and place your target metrics into the Values area to dynamically generate the grouped columns.

Normalize Data and Reshape with a PivotTable

Maintainability Advantage: Using a PivotTable allows you to instantly refresh and restructure the column groups whenever your source data updates, without touching any VBA code.

Solution 2

Use VBA Dictionary to Parse and Group Columns

If a programmed solution is strictly required, use the VBA Split function alongside a Dictionary object to parse the values and write them systematically to an array.

Advanced Data Processing in WPS Office

Easily Parse and Reshape Data with WPS Spreadsheet

WPS Spreadsheet provides robust tools for data parsing, including Text to Columns, PivotTables, and full VBA macro support, allowing you to easily handle complex delimited values.

1. Open your Dataset: Launch WPS Spreadsheet and open the workbook containing your semicolon-delimited values.
2. Parse the Text: Navigate to the Data tab and click 'Text to Columns' to split your complex values by semicolons into standard cells.
3. Generate the PivotTable: Select the newly structured columns, click 'PivotTable' under the Insert tab, and choose a new worksheet location.
4. Group the Columns: Drag your categories into the Columns area to dynamically generate the requested header groups.

Fully compatible with Microsoft Excel (.xlsx, .xlsm, .csv) macro-enabled formats.Built-in advanced PivotTable features for quick data reshaping.Supports powerful VBA macros for automating text parsing without extra plugins.Lightweight interface that handles large datasets smoothly.

microsoft office alternative - wps office

Frequently Asked Questions

Why is a normalized data structure better than a pivoted layout in VBA?

A normalized structure separates data points into distinct columns (e.g., size, prefix, value), making it significantly easier to filter, sort, and update. A hard-coded pivoted layout created via VBA is rigid and requires extensive script rewrites if the data format or categories change.

How do I enable the Scripting.Dictionary in VBA?

To use the Dictionary object, open the VBA Editor, go to Tools > References, and check 'Microsoft Scripting Runtime'. Alternatively, you can use late binding by declaring the object as `CreateObject("Scripting.Dictionary")`.

Can I parse semicolon-delimited values without using macros?

Yes. You can use the 'Text to Columns' wizard located in the Data tab. Choose 'Delimited', select 'Semicolon' as the delimiter, and the software will split the text across multiple columns automatically.

Will running a VBA macro overwrite my original worksheet?

It depends entirely on how the macro is written. To preserve original data, ensure your VBA script directs the parsed output to a new array and pastes it into a freshly inserted worksheet or a designated output range away from the source.

Excel & Data Problems