Merging Text With CONCAT

Spreadsheets often store related text in separate cells: a first name in one column, a last name in another. To show them together as one value, you join (or concatenate) the text.

Joining text lets you build full names, addresses, product codes, or email subjects without retyping anything. When the source cells change, the joined result updates automatically.

In this lesson you will use the CONCAT function to merge text from several cells into a single string.

CONCAT takes one or more pieces of text and sticks them together in the order you list them.

The basic shape is =CONCAT(text1, text2, ...). Each argument can be a cell reference like A2, a typed string in quotes like "Hello", or even a range.

It returns everything as one combined string. Below, A2 holds Ada and B2 holds Lovelace.

If A2 is Ada and B2 is Lovelace, then =CONCAT(A2, B2) gives AdaLovelace with no gap.

CONCAT only joins exactly what you give it. It does not add spaces for you.

To fix this, insert a literal space as its own argument. A space is just text, so it goes inside quotation marks: " ".

Any argument wrapped in quotes is treated as literal text and copied exactly into the result.

This lets you weave fixed words between your cells. Suppose A2 is Ada and C2 is Engineering.

The formula below blends a cell, fixed words, and another cell into a tidy sentence fragment.

CONCAT accepts many arguments, so you can chain several cells and separators together.

Imagine building a full name from three columns: first name in A2, middle name in B2, and last name in C2.

Just list each cell with a space between them.

Suppose a product code combines a category letter, a dash, and a number. A2 holds SHOE and B2 holds 4471.

You want the code SHOE-4471. Mix the cells with a literal dash in the middle.

Notice that CONCAT happily joins text and numbers; the number is converted to text in the result.

There is an even quicker way to join text: the ampersand operator &.

It works just like CONCAT but reads more naturally. These two formulas produce the same result:

• =CONCAT(A2, " ", B2)
• =A2 & " " & B2

Many people prefer & for short joins because it is faster to type.

You may see an older function called CONCATENATE in some spreadsheets. It also joins text, but it cannot accept ranges and is kept only for backward compatibility.

CONCAT is the modern replacement: it does everything CONCATENATE does and also accepts whole ranges like A2:A5.

For new formulas, prefer CONCAT or the & operator.

Because CONCAT accepts ranges, you can merge a column of values in one shot. If A2:A5 holds the letters C, O, D, E, then =CONCAT(A2:A5) returns CODE.

Be careful: with a range you cannot insert separators between each item. If you need a delimiter between values, you will want TEXTJOIN, which is the next lesson.

A common real task is building emails from name columns. Say A2 is ada and B2 is lovelace, and every address ends in the same domain.

Combine the cells with a dot and a fixed domain string.

The result is ada.lovelace@coddykit.com, and it updates whenever the name cells change.

If a source cell secretly contains an extra space (for example Ada with a space at the end), your joined text inherits it: Ada Lovelace becomes Ada Lovelace with a double gap.

You can clean each cell as you join by wrapping it in TRIM, which removes extra spaces.

This keeps your combined output neat even when the data is messy.

Test your understanding of joining text with CONCAT.

You learned how to combine text from multiple cells:

• =CONCAT(text1, text2, ...) joins values in order.
• It adds no spaces, so include " " yourself.
• Quoted text becomes literal text in the result.
• The & operator is a quick alternative.
• CONCAT accepts ranges, but cannot place separators between range items.

Next, you will join many values with a delimiter using TEXTJOIN.