How to Merge Incorrectly Split Excel Columns with Delimiters
Question details
The user needs to correct inconsistent comma-splitting in daily reports that shifts values into wrong columns, particularly when files are combined.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Cleaning up and combining daily report files where data was incorrectly split by delimiters across varying columns and rows.
- Observed behavior
- Data values shift into incorrect adjacent columns because of inconsistent comma delimiters in the source data, causing misalignment.
Identify the specific delimiter causing the split (e.g., a comma) and back up your original daily reports before applying bulk transformations or VBA macros.
Use Dynamic Array Formulas to Rebuild the Row
Ideal for rows where the incorrect split follows a predictable pattern, allowing you to conditionally merge and split the text.
If only specific rows have an extra column due to an improper split (e.g., a comma inside a number), you can use TEXTJOIN and TEXTSPLIT to rebuild the row dynamically.
Check which columns contain the improperly split values (for example, columns D and E).
In a new blank column next to your data, enter a formula that checks for the shift. For example: `=TEXTSPLIT(IF(H2<>"",TEXTJOIN(",",,A2:C2,D2&E2,F2:H2),TEXTJOIN(",",,A2:G2)),",")`. This checks if column H has data (indicating a shift), joins D and E together, and then splits the row back into proper columns.
Drag the fill handle down to apply this correction formula to all rows in your daily report.
Copy the newly generated array, right-click the original data range, and select 'Paste as Values' to overwrite the broken data with the corrected layout.
Use a VBA User-Defined Function (UDF)
Best for complex datasets where the affected columns vary, by automatically detecting and combining adjacent numeric cells.
Fix Data Shifts via Power Query
Useful when combining multiple daily report files into a single query to fix shifts before they load into the sheet.
Clean and Merge Inconsistent Data with WPS Spreadsheet
WPS Spreadsheet provides robust support for dynamic array formulas and advanced macro functionalities, empowering you to quickly fix irregularly split columns in large reports without hassle.
- 1. Open your report: Launch WPS Spreadsheet and open the daily report file containing the incorrectly split data.
- 2. Set up the correction column: Select an empty cell next to your main data table to prepare for data transformation.
- 3. Apply formulas or VBA: Use the built-in TEXTJOIN dynamic array formula or a custom VBA UDF to re-merge the split cells automatically.
- 4. Finalize the cleanup: Copy the corrected output and paste it over the original data as values to permanently fix the layout.
Frequently Asked Questions
Why do my numbers split into different columns when importing reports?
This usually happens when the source data uses a delimiter (like a comma) inside numbers (e.g., 1,000) but lacks text qualifiers like quotation marks. The software reads the comma as a column break instead of a thousands separator.
How can I combine cells only if they contain numbers?
You can write a custom VBA function that loops through the range, checks if `IsNumeric(cell.Value)` is true for adjacent cells, and concatenates them if the condition is met.
What if the incorrectly split columns change rows dynamically?
Using an IF statement combined with TEXTJOIN allows you to test for a condition (e.g., checking if the last column is empty). If the row is shifted, it triggers the combination logic; otherwise, it leaves the row as is.
