ysskrishna logoysskrishna

Chapter 4 of 6 · 3 min

Concatenate and merge: combine values with a separator

Join several values into one merged cell without losing any: separators, trimming, skipping blanks and direction.

Concat is the merge that keeps everything. The sidebar: "Join cell values with a separator, then merge into one cell."

Options

  • DIRECTION: e.g. "Vertical - merge down columns" (join a column's cells) or horizontal (join a row's cells).
  • SEPARATOR: Comma ( , ), semicolon, pipe (|), dash, newline, or a custom character (from the listing).
  • ALIGNMENT.
  • ADVANCED OPTIONS: Trim whitespace from each value, Skip empty cells (both shown switched on in the listing's screenshot).
  • Button: Concatenate & Merge.

Example

Selection A2:A4, vertical, separator comma, trim and skip on (sample data):

BeforeAfter
asha@acme.ioasha@acme.io, raj@acme.io (one merged cell spanning A2:A4)
(blank)
raj@acme.io

Without Skip empty cells you would get a double separator for the blank. Without Trim whitespace the stray spaces stay inside the joined text.

Choosing a separator

SeparatorGood for
CommaLists people read; CSV-style values
SemicolonEmail "To" fields (Outlook)
PipeValues that contain commas
NewlineA readable stacked list inside one cell
Custom/ for paths, · for labels

Concat versus a formula

=TEXTJOIN(", ", TRUE, A2:A4) gives the same joined text in another cell and updates live. Use the formula when the source changes; use Concat when you want the final layout in place, merged, without a helper column.

Horizontal concat

Set DIRECTION to horizontal to join across a row, for example first name, middle name and last name into one merged cell with a space as a custom separator. The Concat_Horizontal tab in the examples workbook shows it.

Step by step: stack a project's tags

  1. In A2:A5 put tags (sample): sheets, free, (blank), add-on.
  2. Select A2:A5. Open Concat.
  3. DIRECTION vertical, SEPARATOR newline, both advanced toggles on.
  4. Concatenate & Merge. One cell lists the three tags on separate lines.

Practice: one cell per order

Sample data, one row per item, with a stray blank and a padded value:

OrderItem
1001Mouse
1001Keyboard
1001
1002Monitor
  1. Select B2:B4 (order 1001's items).
  2. Open Concat. Set DIRECTION to vertical and SEPARATOR to Comma.
  3. Turn on Trim whitespace from each value and Skip empty cells.
  4. Click Concatenate & Merge.

Expected result: B2:B4 becomes a single merged cell containing Mouse, Keyboard. Without the two toggles you would get Mouse, Keyboard , with doubled spaces and a trailing separator for the blank row.

Repeat for each order. For long lists, sort by Order first so each order's rows are contiguous, then work down the sheet one order at a time.

Choosing between Concat and a formula, in practice

Use the add-on when the result is a finished, static cell for a report you are about to share. Use =TEXTJOIN(", ", TRUE, B2:B4) when the source rows will keep changing and the joined cell must update itself; the TRUE argument skips empty cells, like the toggle. A formula result cannot sit in a merged cell spanning its own input range, so with formulas you write the result into a new column instead.

Common mistakes

  • Concatenating across different orders because the selection crossed a boundary. Check the range shown in the sidebar header before you click.
  • Choosing a separator that already appears in the values (commas inside "Acme, Inc."), which makes the joined text impossible to split back later. A semicolon or a new line is safer for free text.
  • Expecting to undo it with Unmerge alone: unmerging gives you the joined text in the top cell, not the original separate values. Keep the source tab, or use Undo straight away.

Quick check

  1. Which option prevents ", ," in the result?Skip empty cells.

  2. Values contain commas. Which separator?Pipe (or another character that doesn't appear in the values).

  3. The source updates daily. Concat or TEXTJOIN?TEXTJOIN, so the result stays live.

Continue exploring

ProductGoogle WorkspaceGoogle Sheets Merge Tools

Google Sheets Merge Tools

Repeatable merges in Google Sheets, without the misclick marathon.
Google Sheets Merge Tools
Google Workspace Add-ons
Google Sheets
Google Apps Script
ProductGoogle WorkspaceGoogle Sheets Tab Manager

Google Sheets Tab Manager

Your workbook outgrew the tab bar. Clean it up in batches.
Google Sheets Tab Manager
Google Workspace Add-ons
Google Sheets
Google Apps Script
CourseCover image for Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Table Converter for Google Sheets: Import and Export Markdown, JSON, HTML and CSV

Import Markdown, JSON, HTML and CSV tables into Google Sheets without broken columns, and export any range as clean Markdown, JSON, HTML or CSV.

Google Sheets Table Converter
Google Sheets
+1

6 chapters · 16 min

BlogCover image for How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

How to Delete Multiple Tabs in Google Sheets at Once (Bulk Hide, Rename, Sort)

Google Sheets Tab Manager
Google Workspace Add-ons
+1
2 min read
ProductGoogle WorkspaceGoogle Sheets Text Power Tools

Google Sheets Text Power Tools

Fix messy sheet text in place. Case, trim, strip, and extract without leaving the grid.
Google Sheets Text Power Tools
Text & Data Conversion
Google Workspace Add-ons
Google Sheets
CourseCover image for Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Sheets Tab Manager: Delete, Hide, Rename, Colour and Sort Tabs in Bulk

Delete multiple tabs in Google Sheets at once, hide and unhide in bulk, rename with find and replace, colour and sort tabs A–Z with the free Sheets Tab Manager.

Google Sheets Tab Manager
Google Sheets
+1

6 chapters · 24 min