Data Import & Export

Combine Column B Text by Group and Remove Blank Rows in Excel

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 870 views

Question details

Combine text in column B under the most recent nonblank value in column A and remove the extra grouping rows.

Combine Column B Text by Group and Remove Blank Rows in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A worksheet has dates or categories in column A, with related text continuing in column B on rows where column A is blank.
Observed behavior
The desired result is one row per group, with related column B text joined together and no blank grouping rows.
Before you start

Give columns A and B headers, such as Group and Text, and ensure each group starts with a nonblank value in column A. Check whether the same label appears in separate blocks: grouping by label will combine those blocks.

Solution 1Recommended

Combine Grouped Text with Excel Power Query

Use Power Query to create a refreshable table with one row per group label.

This method combines all rows with the same group label, even if they occur in separate blocks. It excludes rows before the first group label.

1
Load the source data

Select the data in columns A and B, including the Group and Text headers. Choose Data > From Table/Range, confirm that the table has headers, and select OK.

2
Fill down group labels

In Power Query Editor, select the Group column and choose Transform > Fill > Down. Use the Group filter to remove any remaining null or blank values.

3
Add an index

Choose Add Column > Index Column > From 1. The index preserves the original text order within each group.

4
Group the rows

Select Group, choose Home > Group By, and group by Group. Name the new column Rows and select All Rows as the operation.

5
Combine the text

Choose Add Column > Custom Column, name it Combined Text, and enter: Text.Combine(List.Transform(List.Select(Table.Sort([Rows], {{"Index", Order.Ascending}})[Text], each _ <> null and Text.Trim(Text.From(_)) <> ""), each Text.From(_)), " "). Select OK.

6
Load the result

Right-click the Rows column header and choose Remove. Select Home > Close & Load to create the cleaned table in Excel.

Combine Grouped Text with Excel Power Query
Refresh after edits: When the source table changes, right-click the loaded result and select Refresh. Power Query creates a separate result rather than deleting rows from the source.
Combine grouped text in WPS

Combine Text Blocks in WPS Spreadsheets

For a smaller worksheet, use TEXTJOIN to combine each block of column B text, then filter out the blank grouping rows in WPS Spreadsheets.

  1. 1. Open the worksheet: Open the Excel file in WPS Spreadsheets and identify the column B row range belonging to each nonblank label in column A.
  2. 2. Join the first block: In an empty column on the first group row, enter a formula such as =TEXTJOIN(" ",TRUE,B2:B4), adjusting the range to include all text rows in that block.
  3. 3. Repeat for each group: Enter a TEXTJOIN formula on each remaining group row, changing the column B range to match that group.
  4. 4. Keep the combined text: Copy the formula results and use Paste Special > Values to replace the original column B entries on the group rows.
  5. 5. Remove blank grouping rows: Select the table and use Data > Filter on column A. Filter for blanks, select the visible blank grouping rows, and delete them; then clear the filter.
Combine text from each group with a space separatorFilter blank group labels to identify rows to removeOpen and save Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
QA img-9

Frequently Asked Questions

Will Power Query combine the same label if it appears in separate blocks?

Yes. Group By combines all rows with the same Group value. If separate occurrences must remain separate, add a block identifier before grouping and group by that identifier as well.

How do I update the combined table after adding data?

Add the new rows to the source Excel table, then right-click the Power Query result and select Refresh.

What happens to rows before the first group label?

Fill Down cannot assign them a group because no earlier label exists. The Power Query filter in this solution removes those rows from the result.

How can I use a comma instead of a space between text entries?

In the Power Query custom column formula, replace the final separator " " with ", ". In WPS, use =TEXTJOIN(", ",TRUE,B2:B4) with the appropriate range.