How to Combine Matching Lines from Two Excel Cells Using VBA
Question details
The user needs to combine corresponding text lines from two separate cells, where each cell contains multiple lines separated by line breaks.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Merging multiline data from two parallel cells so that the first line of Cell A pairs with the first line of Cell B, the second with the second, and so on.
- Observed behavior
- The text lines are currently separated across two distinct multiline cells, and standard concatenation merges the entire cell blocks rather than joining them line-by-line.
Verify that both target cells contain the exact same number of text lines. Because this solution utilizes custom VBA, ensure your spreadsheet software supports macros and that you save the document as a Macro-Enabled Workbook.
Create a VBA User-Defined Function (UDF)
Using a custom VBA function is the most practical way to dynamically split, match, and combine multiple lines of text between two cells, especially when dealing with a variable number of rows.
While complex formulas can theoretically achieve this in newer spreadsheet versions, a VBA User-Defined Function (UDF) is far cleaner, more reliable, and works seamlessly regardless of how many line breaks exist inside the cells.
Press ALT + F11 on your keyboard to open the VBA editor in your spreadsheet application.
In the top menu, click 'Insert' and select 'Module' to create a blank workspace for your custom function.
Define a custom function (e.g., Function CombineCells). Use the VBA 'Split' function to separate both cell values by line breaks (vbLf or Chr(10)). Loop through the resulting arrays to join the corresponding lines together, then output the combined array.
Return to your main worksheet. Select an empty cell and type your new formula, such as =CombineCells(A2, B2), to instantly merge the matching lines.
Use WPS Spreadsheet for Advanced VBA Tasks
WPS Office fully supports VBA macros, allowing you to easily build and run user-defined functions for complex tasks like merging multiline cells. Enjoy robust macro support within a lightweight, intuitive interface.
- 1. Open WPS Spreadsheet: Launch WPS Office and open the workbook containing your multiline data.
- 2. Access the VBA Environment: Navigate to the 'Tools' tab and click on 'Visual Basic Editor', or simply press ALT + F11.
- 3. Insert the Function: Create a Module, paste your custom line-combining script, and apply the formula directly to your cells.
Frequently Asked Questions
Why does my VBA function return an error when combining the cells?
This usually occurs if the two referenced cells do not have the same number of lines. Ensure that both cells contain an equal number of line breaks so the loop can perfectly match each line.
Can I use standard formulas instead of VBA to combine these lines?
Yes, but it is highly complex. In the latest spreadsheet versions, you might combine functions like TEXTSPLIT, MAP, and TEXTJOIN, but a VBA solution remains much more backward-compatible and reliable for variable line counts.
How do I insert a line break inside a spreadsheet cell?
To add a manual line break while typing inside a single cell, press Alt + Enter on Windows or Option + Return on Mac.
