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

*Ever wondered how to go from a bunch of cells with text to one big combined text?* Like this:

Well, there is a simple trick, shared by Grant with us in the *What is the coolest Excel trick you learned in 2013?* post.

### Quick and easy way to combine bunch of text values

- Let say the cells you want to combine are in B2:B19.
- In a blank cell, where you want to concatenate all the values type
- =CONCATENATE(TRANSPOSE(B2:B19))
*Don’t press enter yet.*- Select the TRANSPOSE(B2:B19) portion and press F9. (related: debugging formulas using F9 key)
- This replaces the TRANSPOSE(B2:B19) with its result
- Now remove curly brackets { and }
- Enter
- Done!

See this demo to understand.

### Bonus tricks

- If you cannot use F9 for any reason, use
**CTRL+=** - 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 & “,”)
- 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.

### More on dealing with text using Excel

Here are a few more tips on working with text values in Excel.

- Finding patterns in text
- Extracting a portion of text
- Converting text to sentence case
- Separating user names & domains from email addresses
- Sorting text values using formulas
- Initials from names
*More text processing tips, quick tips.*

### Thank you Grant

Thanks Grant for sharing this trick with all of us. It is a time saver for sure.

*If you like this tip, say thanks to Grant.* Also, in the comments, tell us how you combine text values and what other tricks you use.

### Do you want to be awesome in Excel?

**Here is a smart way to become awesome in Excel**. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing **95 Excel tips & tricks**. Please sign-up below:

Your email address is safe with us. Our policies

### Leave a Reply

42% of the world goes to polls around a pie chart – Like it or hate it? |
Find first non-blank item in a list with formulas |

## 102 Responses to “Quickly combine text in multiple cells using this trick! [Formulas]”

Great !

Awesome Thanks for sharing

Neat trick, thanks for sharing.

It is very helpful for me.. thanks for sharing this tip

Fantastic! This will save me a huge head ache in the future. Thank you.

In my excel version (2010, polish), is giving “\” instead of commas….

My excel gives me only “.” not “;”.

(excel 2010 german)

The use of ; and , are used predominantly in English only

You will need to customise to suit your language

To find out what your locale is

In a cell Type =A1:E1

Don’t press Enter but Press F9

Excel will display something like ={1,1,1,1,1}

Take a note of the separator character which is , in English

In a cell Type =A1:A5

Don’t press Enter but Press F9

Excel will display something like ={1;1;1;1;1}

Take a note of the separator character which is ; in English

Note in above the ,’s seperate Columns and the ;’s separate Rows

Use whatever Excel shows you in your language

Strangest Excel behaviour:

In Excel 2003 and 2010, combining text from cells in a row works perfectly with the trick described by Chandoo. When combining text from cells in a column, skip the whole “transpose” thing. Just type =CONCATENATE(A1:K1), press F9 and remove the {} brackets, that’s it.

Seems to be just the other way around in Excel 2013…

That doesn’t quite work because just using CONCATENATE results in

=CONCATENATE({“oh”;”combining”;”a”;”bunch”;”of”;”texts”;”into”;”one”;”big”;”texts”;”is”;”so”;”painful”;”well”;”not”;”any”;”more”})

The ;’s will not work.. using TRANSPOSE with CONCATENATE makes them ,’s instead, which does work. (See Hui’s post regarding ,’s and ;’s as separators (,’s for columns, ;’s for rows)

Wonderful technique.. Welldone !

Nice trick! I always use the following trick though:

1. In C2, enter =C1 & B2

2. Copy down to C19 (C19 now has the info you want)

3. Copy C19 and paste as values and delete the rest

I think I still prefer this, since you have more flexibility when, for instance, the words don’t have end with a space character. In that case you can just type =C1 & B2 & ” “

Also Very Good- and allows you to leave the formula in place for automatic updates in case the data changes ! Thanks for the tip!!

nice trice….

Then just do this: =CONCATENATE(TRANSPOSE(B2:B19&” “)

Then you’ll have the spaces with the above formula as well.

This is great!

For some reason it is vital to leave out the last round bracket in the TRANSPOSE function before presing F9 button, otherwise it won’t work.

cool

Very cool trick!

Thanks for sharing that.

I have been using a similar approach for doing the same thing.

Let’s see which one is of more convenient to you.

Using the example the above example:

1) In a blank cell, Input =B2:B19 (do not press enter)

2) Press F9

3) Remove the “=”, “{“, “}” manually

4) Press Ctrl H (Find and Replace)

5) Find ” and replace with nothing

6) Find ; and replace with your delimiter

7) Enter

8) Done

Great Tip Grant. Thanks for sharing Chandoo… I will publish it in my blog http://www.exceltoxl.com too.

I’d just copy the data to the formula bar using the office clipboard…

1. click arrow in bottom right corner of clipboard section of ribbon.

2. select B2:B19 and choose Copy

3. select new cell and click the formula bar

4. click the icon with the copied data from the clipboard

5. Choose Replace… Find: [Ctrl+j] Replace [ ] OK.

(The last step replaces line breaks with spaces or required delimiter.)

Awesome Tricks.

This does not work in Excel 2010 as described in the posting or the responses.

@Tom… I have tested it with 2010 too, it is working alright. Can you tell me which locale you are using? I am using English (EN-US).

@Chandoo This is a great tip! I didn’t know this. Thx.

@JLeno I use your method as well when I need to add a comma or space.

Cool indeed Grant!.

I like this

Thanx Chandoo

Thanks Chandoo. Very nice tip. How did you discover this?

This is an excellent article – Thank you Chandoo. I often need to do this in my work but have an alternative method for relatively short text concatenations using the JUSTIFY feature from within the EDITING ribbon. It is found by clicking the down arrow to the right of FILL and selecting JUSTIFY, at the end of the presented list.

To use this feature, select all the text plus the column immediately to the right eg: if text was A1:A10, you would be selecting A1:B10. Now select the arrow to the right of the FILL command in the EDITING panel. Choose JUSTIFY. The text has now been concatenated, with spaces but probably into several rows. To place the text into only one cell, either widen the right hand column to the width you believe the text would fill and repeat the process. Alternatively, you could select a larger number of columns and then select JUSTIFY. This would place as much text as it could in the first cell, with the remainder in the cells below.

The JUSTIFY command has the advantage of being fully ‘UNDO’able and can be coded in VBA to create a tool which might insert a new column to the right, justify selected text and then clean up the screen.

this is so interesting!!!! thanks for sharing!

finally I have a sense of “justify”.

That is awesome. Never knew about ‘justify’. Glad I ran across this!

great…..

thanx for sharing

COPY all the list [B2:B19] in EXCEL

PASTE it in NOTEPAD

SELECT all and copy from Notepad

Paste in Excel Cell {edit}/ [F2] PASTE.

” Try This “

x-cellent

Awesome! thank you for sharing! you’ve just saved me bunch of time!

Very nice !

I love it

beautiful tips, thanks grant & chandoo

[…] Quickly combine text in multiple cells using this trick! Formulas: Useful Excel wizardry […]

[…] of creating a long concatenation formula, Chandoo shares a quick trick for combining the values in multiple […]

Thank you. You just made my week.

Please watch short vid http://bit.ly/ConcTran

This uses the original tip but comes up with a formula using cell references. Note that this uses & as well as concatenate so that there is one argument in Concatenate for each cell reference and a separator. But the same method applies by using b,c5 instead of b&c5 but now there will be a separate entry for each ref and for each separator.

Regards

Brian

I love this trick/post very much! Coincidently, I recently run into a similar situation, but with repetitive groups all one column. I need to concatenate every 8 rows into one cell, and there are about 2000 rows, and only one column of text and number. How can I use this trick for every 8 rows? Without entering this formula 2000/8= 250 times? Thank you!

Everybody also could try “phonetic” to complete this like “=phonetic(B2:B19)”

This trick works well if we are selecting the values (to be concatenated) from one particular column (say A1:A30). All the values are present in column A and this is where the transpose function takes it’s eminence. But if we want to do the same thing in a row (say row 1, data from A1:K1), this trick throws an error because the seperator would be ; for traspose function.

We can overcome it by just removing the transpose function and make do with concatenate function only by following the same steps.

i.e.; =concatenate(A1:K1), now select A1:K1, press F9 and replace curly braces with ( ). There you go.. Enjoy..

Great! Thanks buddy

Thank you so much Grant. This really helped me with my school work

That’s one way to do it, I suppose.

Really, the issue is that the =CONCATENATE function that comes with Excel is pretty useless. For just 3 fields, you can just as easily type =A1&A2&A3 as

=CONCATENATE(A1, A2, A3)

Years ago, I wrote the below to address this issue.

The format is =CONCAT(A1:A3)

Function concat(avec As Variant, Optional CHAR2INS As String, Optional trim As Boolean) As String

‘========================================================

‘ this improves on the =concatenate function

‘ Arguments are:

‘ AVEC – this is the vector to be concatenated

‘ CHAR2INS – is the charachter to insert between cell items. If this is

‘ Left blank, Nothing Is inserted

‘ TRIM – whether to trim excess CHAR2INS

‘========================================================

Dim i, j, counter, Total As Integer

Dim numrows As Integer

Dim numcols As Integer

Dim Temp As String

Temp = “”

numrows = avec.Rows.Count

numcols = avec.Columns.Count

Total = numrows * numcols

For j = 1 To numrows

For i = 1 To numcols

‘ skip blanks

If avec(j, i) = “” Then

GoTo 99

End If

‘ don’t put a character after the last item

counter = counter + 1

If counter = Total Then

Temp = Temp & avec(j, i)

Else

Temp = Temp & avec(j, i) & CHAR2INS

End If

99 Next i

Next j

‘ this line takes out unprintable characters

concat = Application.trim(Temp)

If trim = True Then

concat = Application.trim(Temp)

Else

concat = Temp

End If

End Function

This is really helpful

Thank you..!!! 😉

Thanks for this! Saved me

hoursof work!Thanks so much for letting us know about this trick!!

I didn’t know that concatenate has a limit for the number of arguments until I hit that number and was told I have typed in too many arguments in the function. Is there an alternative for this?

@Fred

Instead of =Concatenate(A1, A2 …A100)

you can use =A1&A2&A3 … &A100

or you can use a UDF like those described at:

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Read the comments as well as there are several great additions to the basic code

eg: =Concat(A1:A100,”,”) which will concatenate the values from A1 to A100 with a Comma in between each character

=Concatif(B1:B5,A1:A5,1) which will concatenate the values from A1 to A5 where the corresponding cell in B1:B5 is = 1 etc

Thank you so much!!!

Wow Amazing, very handy.

Great Man,

Thanks!

BR/Syed Bilal

Hi, what if the information in the cells are numbers? I tried using this trick A1&B1 but the numbers can’t be added anymore using AutoSum. In other words, the numbers weren’t considered as numbers by Excel. What do I do?

@Reina

When I use =A1&A2 with 1 in A1 and 2 in A2 I get 12 which is what I should get

can you explain what / how your not getting the same answer?

Hi, I finally found the answer. I just had to put =VALUE(A1&B1) so that the information in the cells will still be considered as numbers.

Hi thanks for wonderful examples.

I want to combine text strings from different cell with ,’s as delimiter and ‘and’ before the last text.

Forexmaple a, b, c, and d

Regards

Rohit

@Rohit

There is a post which discusses a Concat Function here:

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

You could use this as part of a formula

eg: =Concat(A1:A10,”,”)&” and “&A11

If you would like me to modify the concat function to do that let me know

Hi Hui

Thanks for your swift reply and support. Actually I am not the VB expert and hecne is there any other formulas that I can use to make dynamic excel!

Regards

Rohit

@Rohit

The technique in the post doesn’t even add “,” to the data as you requested

I am Happy to assist you adding this to a file if you want

Please email me or post here

Brilliant!!! Thx for sharing!

Awesome Chandoo – You are my Excel Hero. Thanks for sharing

Worked a treat. Many thanks, Rachael

Delimeters do not work, just throws up an error.

Worked it out. The example that looks like this:

TRANSPOSE(B2:B19 & “,”)

Write it like this instead:

TRANSPOSE(B2:B19&“,”)

WOW! Excellent trick!!! Thank you!

Thank you so much — exactly what I needed

Thanks.. Its worked for me and reduced lot of time 😀

hello, i’ve seen and using all this examples and methods and are very useful but in my case it doesn’t mach what i need it for i you guys can give me hand here…

i need to create a list of attributes in just one cell separating each other with an “:” using the a2:cv2 just notice that in some of the columns are empty because the filter in that product doesn’t apply.

for example

a2= black, b2= , c2= small… cv2= cube

so this should look like this at the end. “Black:Small:Cube”

but with formula =concadenate(a2:cv2&”:”)

= “Black::small::::::::::::::::::::::::::::::::::::::::::::::::cube:”

so i’m looking to do this for each row taking in consideration each column selected to create the right attributes applied for the product and no extra space (knowing that an extra space is taked for my upload system that each “:” is a new attribute ) and also when i do this excel make an auto correct option but not copying the formula and just the result in all the rows and i need to make a copy of the original formula and make it valid per each one on the following 3k rows..

Thanks for your collaboration

😀

This is very neat. What’s the delimiter for a new line in Excel 2013? Basically in the above example replacing the & “,” with a new line delimiter? I want every column value (each word) to be in a separate line in the same cell.

I got it. I used the CHAR(10) and then manually set the wrap text for the cell at the end

how can we use F9 in muti cells together …i have 100 cells and every cell has array which is working fine but i have to go to each cell and press f9 to see the result and then concatenate. can we not use F9 just once for all cells and then do the rest thing

Can someone suggest shorter way of Exactly opposite of what Chandoo made us learn here, i.e. Seperate text given in “different rows in a single (same) cell (separated by ALT+ENTER) into different rows altogether”?

Its useful

thank you so much , same function is sold online , is there another trick to do the opposite ? i have many values in one cell separated only by space , I tried the column to text but no use , thank again

I was so excited when I saw this and thought how perfect it would be for me to put together the missing records for my IRS file. Alas, since the file record is 750 characters long, and each character is in a row of its own, I have too many arguments for the function.

Guess I’ll get lots of practice doing it in little bites, then combining those little bites into bigger and bigger bites, until it’s one long record. Curse you IRS!

Nice article

I need t to merge rows in one of my excel sheets, so after long googling this simple function has been born and I name it MergeCells(). Calling it is simple, select the range you want to merge, and set the delimiter.

Example:

MergeCells(A23:Q23, “-“)

[vb 1=”vbnet” language=”,”]

Public Function MergeCells(cellsRange As range, delimiter As String) As String

Dim cell As range

Dim mergedCells As String

mergedCells = “”

For Each cell In cellsRange

mergedCells = mergedCells & delimiter & cell.Value

Next cell

MergeCells = Right(mergedCells, Len(mergedCells) – 1)

End Function

[/vb]

When I try this I get a Value error if i dont do the F9. It works with f9, but dont want to write the formula for 300 rows. can anyone help. its 2007

the formula i used is = CONCATENATE(A4:U4 & “,”)

Also, In that row, few cells have no value, and few are numbers and text. Is that a problem?

@Pranitha

Have a read of:

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Particularly my comments at the bottom where I discuss a number of alternative’s

Thank you Hui.. That is a lot detailed.. very helpful

holy carp I’m in love..! Thanks!!!

Chandoo, Great tip!

Thank you SO MUCH for this tip. I’ve been so frustrated with how the CONCATENATE command won’t accept a range of cells, and this is quick and easy to implement, even with the ;–, substitution. You made my life a lot easier!

Thanks a lot bro, helped me save lot of time.

I have another issue with concatenate .. hoping some one can help …

data is in the below form –

112233 SAP BO

112233 SAP BW

123456 Teradata

123456 Qlikview

123456 Talend DI

like above example a huge data with employee IDs in one column and Skill name in another column.

number of times the employee id repeats itself is not constant.

Is there some way i can concatenate using some loop or logic.

Please help.

@Ayush

You can obtain a list of unique names using an Advanced Filter

then I’d suggest using a Concatif() UDF

Read about it here and search for Concatif in the comments

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

There are several versions and I’m sure one can be used to help you

If you come up stuck I’d suggest posting a question at the Chandoo.org Forums

http://chandoo.org/forum/

Attach a sample file to make giving a precise solution easier.

Hi there,

This was very helpful… Do you have a solution if i am trying to concat a string across many worksheets?

For example, cell B4 on Sheet1 would concatenate all the text from cell B4 on Sheets 2:8.

can anyone help?

thanks

Thank you so much. I wanted to concatenate a large number of postcodes for use on a survey, and now thanks to you I don’t have to enter them all.

Awesome work!

Hi, what VBA code do you amend to include a delimiter of “,”

Kind Regards

Brad

This tip is great! Saves a lot of time! Thanks

THIS IS AWESOME!

You freaking saved me! Awesomeeeeee!!!!!!

nice!

I searched all around and this was the best resource. THANKS!!!!

Great . Thanks alot

Hi there

the original formula is very useful and thanks for sharing.

I have a spreadsheet where I need to apply this to over 3,000 rows where the following:

Column A Column B (the information I’m concantenating)

XXXXX1 5 rows

XXXXX2 10 rows

XXXXX3 15 rows

Result:

XXXXX1 (information from 5 rows)

XXXXX2 (infomation from 10 rows)

etc

How do I create the formula and apply it to the whole spreadsheet. What I’m getting stuck on is column B has different amounts and I’m not sure how to copy a formula and apply it again when you need to press F9 and remove brackets.

Any help would be greatly appreciated.

Looking for help turning this:

RR686

VK165

K2418

L7192

P0636

P4303

P4802

P6084

P7390

P8058

Into these:

‘RR686′.’VK165′.’K2418′.’L7192′.’P0636′.’P4303′.’P4802′.’P6084′.’P7390′.’P8058’.

@Prany

Have a read of:

http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

Also there are some gems buried in the comments

assume your data is in A column i.e. “A2:A11″. type in B2 cell…=B1&”‘”&A2&”‘.” then drag to B2 cell till B11.

you will get your result in B11 cell.

OMG Thanks! Saved me a lot of time, much better than the VBA approach suggested elsewhere.! Thanks!