Scribd
Upload
118 views1 page

Excel Text Combining Guide

The Concatenate formula is used to combine multiple text values into a single cell. It adds text values from multiple cells or strings together. The syntax is =CONCATENATE(text1, text2, etc.) which joins the text values provided. Quickly combining multiple cells can be done using =CONCATENATE(TRANSPOSE(range)), selecting the TRANSPOSE part and pressing F9 to convert it to values, then removing brackets. This allows concatenating across rows or columns without needing to transpose.

Uploaded by

ramu146
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
118 views1 page

Excel Text Combining Guide

The Concatenate formula is used to combine multiple text values into a single cell. It adds text values from multiple cells or strings together. The syntax is =CONCATENATE(text1, text2, etc.) which joins the text values provided. Quickly combining multiple cells can be done using =CONCATENATE(TRANSPOSE(range)), selecting the TRANSPOSE part and pressing F9 to convert it to values, then removing brackets. This allows concatenating across rows or columns without needing to transpose.

Uploaded by

ramu146
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

What is the use of Concatenate formula?

Adds a bunch of text values to one another

Syntax of Concatenate formula:


concatenate(this, [to this..])

Examples of Excel Concatenate formula:


concatenate("one ","big ","text") = one big text
concatenate(A1,A2,A3) = adds the text values in A1,A2 and A3

Quickly combine text in multiple cells using this trick! [Formulas]

1. Let say the cells you want to combine are in B2:B19.


2. In a blank cell, where you want to concatenate all the values type
3. =CONCATENATE(TRANSPOSE(B2:B19))
4. Don’t press enter yet.
5. Select the TRANSPOSE(B2:B19) portion and press F9. (related: debugging formulas using F9 key)
6. This replaces the TRANSPOSE(B2:B19) with its result
7. Now remove curly brackets { and }
8. Enter
9. Done!

Bonus tricks

1. If you cannot use F9 for any reason, use CTRL+=


2. If you want to add a delimiter (like space or comma) after each item in the text, you can use
TRANSPOSE(B2:B19 & ” “) or  TRANSPOSE(B2:B19 & “,”)
3. If the range you want to concatenate is across columns (Say A1:K1), then you can skip the
TRANSPOSE formula and write =CONCATENATE(A1:k1), Select A1:K1 and press F9, remove {}s.

Keep in mind

Since F9 replaces formulas with values, if your original data changes, then you must re-write the
CONCATENATE(TRANSPOSE(…)) again.

If you would rather keep the formulas alive, then use CONCAT() UDF. It takes a range and a
delimiter and spits out combined text with ease.

You might also like