## Concatenate a cell range without vba in excel

Joining many text strings in excel is not easy. The Concatenate function accepts only a single cell reference in each argument.

Example,

=Concatenate(A1, A2, A3) + ENTER

To quickly select cells you can press CTRL and click on cells you want to be included in the concatenate function.

Example,

- Select a cell
- Type
*=concatenate(*in formula bar - Press and hold CTRL button and click cells to be included.
- Release CTRL button
- Type ) in formula bar and press Enter

The above example works if you don´t have so many cells to add. A large range would be tedious to add, clicking on each cell.

### Concatenate a cell range

Here is a larger range, A1:A50 with some values.

Let´s concatenate these values:

- Select cell B1.
- Type
*=A1:A50*in formula bar.

**Edit:***Depending on your regional settings, try this: =TRANSPOSE(A1:A50)* - Press F9.
- Delete curly brackets in formula bar.
- Type
*=Concatenate(*in front of all characters in formula bar. - Type ) at the end in formula bar.
- Press Enter

**Formula in cell B1:**

### Create delimiting character

It would be great if we could have a character separating values. Here is how:

- Select cell B2.
- Type
*=A1:A50&", "*in formula bar.

**Edit:***Depending on your regional settings, try this: =TRANSPOSE(A1:A50)&", "* - Press F9.
- Delete curly brackets in formula bar.
- Delete last delimiting character.
- Type
*=Concatenate(*in front of all characters in formula bar. - Type ) after last character in formula bar.
- Press Enter

**Formula in cell B2:**

Wont work.

A1:A50 will return a row array.

You will need to type =transpose(a1:a50) in cell b1 , say f9, delete the curly brackets and type concatenate

Hi, this does not work in my excel. When I type =TRANSPOSE(A1:A7) in the formula, it brings only the first cell value. On click of F9 also, no change in the value. I tried the steps mentioned by you, but could not make it. Please help me on this. Thanks in advance..

Nandhini,

Type TRANSPOSE(A1:A7) in a cell. Do not press Enter. Press F9.

Oscar, thanks a lot. It works. With a demo you replied and helped me to get knowledge on it. I really appreciate your assistance and thank you so much for your help.

=Transpose() is unnecessary. Just type =A1:A7 and hit F9 BEFORE hitting enter, to get the corresponding values in an array'd list.

Sam,

Thanks for commenting!

It works with my regional settings in excel 2007.

Oscar, In the English version the row array has a ; as a separator and a column array has a , as a separator.

=A1:A10 give ={"name1";"name2";.....;"name10"} etc

Great tip, Oscar. Surprised that Microsoft didn't fix this in the Excel 2010, but go figure. Sam, I think I had the same problem as you and I was running the evaluate (F9) by selecting just the range and not the full TRANSPOSE(A1:A50) in the formula. If you run the evaluate on just the evaluated range of A1:A50, it will keep the semi-colon separator and not use the comma. But if you run the F9 by selecting the function and the range [TRANSPOSE(A1:A50)], it should work. Awesome tip, I have run into this issue many times before. Glad I now know the fix.

Michael,

Thanks!!

[...] Unfortunately you need some vba code to pull this one off: VBA Join function or maybe not, read this: Concatenate a cell range without vba in excel [...]

Great tip thanks! On Office for Mac, use command+'=' instead of F9

Thanks very much. Especially the extra bit adding a separator. Will save me a lot of time.

I had to play with it a bit in Office 2010, but that F9 trick saved me hours.... Nice job !!!

Damien, Ross and Mark

Thanks!!

Thanks man! Great tip!

Nice.Thanks for your help. Only annoying thing is that the formula is limited on 8592 caracters or something like this. otherwise is just perfect with F9 function. Good job. 10x

There's another way to do it. It's a bit more cumbersome but it updates automatically (which this won't do for me) and you don't need to go through the process of selecting each cell (which can take quite awhile if there are a lot of them).

In a new cell, alongside the ones you will concatenate or anywhere else that there's enough space, set it equal to the first cell. Then in the next cell, concatenate that new cell and the second cell. Now just drag it all the way to the end and they'll all be concatenated. (I did it the other way around from the end to the beginning so the resulting string was at the beginning but it should work either way.

Like I said, it's a bit cumbersome but it updates and you don't have to select all the cells. As a plus, you can add onto it or take away from it simply by dragging without having to edit the formula again.

To concatenate e.g. A2 to A100,

the OFFSET function allows for an easier and more robust solution:

In cell B2, write:

=OFFSET(B2, -1, 0) & OFFSET(B2, 0, -1)

This will concatenate the content in the above cell, B1, and the left hand cell, A2.

Using the small cell handle in B2's bottom right hand corner, drag downwards to copy the cell as many rows as desired. In B100, the content of A2, A3, A4 ... A100 will be concatenated.

In comparison to Richard's method, this is more robust because each cell only references itself. Thus, removing a row or reordering/sorting rows will not yield a #REF! error.

In the above example, note that the cell B1 should be left empty.

This was very helpful. Thank you for sharing!

@Richard,

If you do the repeatd concatenations downward, you can display the last one using this formula (adjust the specified ranges as needed)...

=LOOKUP(2,1/(B1:B65535""),B:B)

Richard,

I believe this post describes the same method:

http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/

You can also use an user defined function to concatenate values:

http://www.contextures.com/rickrothsteinexcelvbatext.html

[...] פתרון המשתמש בכלים הקיימים באקסל – קראתי אותו בעמוד הבא. החסרון של פיתרון זה הוא חוסר אפשרות [...]

Great tip! If only I had known this years ago.

You are a genius.

worked great

lets take over the world

Brilliant!

Awesome, you just saved me hours of work!!!!

thanks..gr8 workaround.

This post was sickeningly awesome.

Awesome seriously ... frustration is abated. My work needed I concatenate range of cells with comma. Works amazing. My regional settings needs the =transpose(a1:150)&","

Thanks much

Hello,

thanks a lot for these steps.

It helps do many things faster as other users wrote.

Michal Kalián,

Thanks for commenting!

Genius! excellent

So happy to have found this :)

Sam and Caz,

I am happy you like it!

My problem was a continuously expanding dataset of term deposits from which some values needed to be extracted to a single text string based on date for inclusion in a report. Let's say the dataset occupied columns A, B, C and D. In column E, I created an IF() function to return the report output, including a delimiter, for that entry if it lay in the wanted date range and a null "" string if it did not. The problem then was to concatenate the results in column E. I solved this by concatenating the result in each row with the value in the subsequent row thus, in cell E1, the formula looks like =CONCATENATE(IF(....),E2) where IF(....) is the formula for the output based on row 1. I then used fill down to replicate this formula in cells (E2:En) and the output in cell E1 then contains all that I need for my report. Works for any number of rows but will become slow if too many rows produce wanted output.

Alan,

Thanks for sharing!

[...] Publisher More from patrick dhungel: Python Miscellaneous kindle Sort Share http://www.get-digital-help.com 3 minutes [...]

What if you had multiples columns which you wanted to concatenate individually?

A1 A2

B1 B2

C1 C2

.. ..

A1, B1, C1 etc

A2, B2, C2 etc

Manually doing F2 F9 across multiple columns would be painful. Is there formula or macro that can automate this?

The point of this page is to do it without a macro (vba) and, short of writing one, I don't think it can be done for multiple columns. As to the pain, the method that I and others have used involves putting a formula in only one cell, the top cell of the leftmost column, then using fill down to replicate it in the other cells of that column. Once that is done, select the column that now contains the formulas and fill right to replicate the whole column for the remaining columns. Unless your spreadsheet is huge, this should be relatively quick and easy. If your columns are not adjacent, you will have to use copy and paste instead of fill right.

Cube,

Rick Rothstein (MVP - Excel) posted a user defined function here:

http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/#comment-5003

Great tip, its what Im looking for

I've tried to apply for multiple row but not success. could you kindly guide how to do if it is possible

data as follow:

A B C result

1 100 200 300 100200300

2 400 500 600 400500600

3 700 800 900 700800900

........................

I have a lots of rows, there is any way to apply for multiple rows?

I means, we dont need go to each cell then press F9 then delete curly brackets.

tks so much

DTNAM,

I recommend using Rick Rothstein's (MVP - Excel) user defined function here:

http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/#comment-5003

tks so much, I just want to find out any tips to solve it

You should add a nice formula to have cells report their own reference as a string eg:

=CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROW())

Then you can easily create a string of references for further concatenation eg: put my first formula in A1 and copy the following formula into cell B1 and then copy that through to J1. The desired string is then in J1

=CONCATENATE(A1,",",CONCATENATE(SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1",""),ROW()))

Two formula, no typing: copy paste x3 Done.

richard,

Thank you for you contribution.

I made a similar post a while ago that concatenates values:

http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/

@Rick Rothstein (MVP - Excel)

I found some confuse your recommend as below;

"@Richard,

If you do the repeatd concatenations downward, you can display the last one using this formula (adjust the specified ranges as needed)...

=LOOKUP(2,1/(B1:B65535""),B:B)"

I would like to repeated concatenations downward, as example problem

A B C D expected result

1 2 3 4 1234

M O D C MODC

I follow your recomment

Step 1 = A1:D1 and press F9

Step 2 = {1,2,3,4}

Step 3 = I type concatenate in formula field

By the way, however, I will repeated this formula in new cell (B2:D2) to get above expected result

thank you

Of course we wouldn't need any such tricks if MS wrote a DECENT concatenate function where you simply passed in a range and an optional delimiter.

I believe that the MCONCAT function (3rd-party add-on) does just that. (Or I'd write my own UDF in most cases)

But if you are still wanting to create CONCATENATE formulas without VBA then certainly the little F9 trick can save time. Thanks !

[…] פתרון המשתמש בכלים הקיימים באקסל – קראתי אותו בעמוד הבא. החסרון של פיתרון זה הוא חוסר אפשרות […]

RE: Concatenate a cell range without vba in excel

Thank you! WORKS WONDERFULLY. I'm not programming-literate so, although I have had occasion to try to understand & tackle VBA solutions, a good Macro saves me tons of time & aggravation.

(Note: The only problem I have is that I have numerous cells in the range that very frequently have null (0) values, but I DON'T want to see any of the zeros in the concatenated string/result (& there's no need for anything in the string to indicate the place that 0 would occupy). (I've set my worksheet Display Options to NOT show 0 values.) Any suggestions would be welcome & appreciated; but as it is, I can easily work around this minor issue. SO THANK YOU FOR A GREAT TIP My work-around for the 0's is to just add a step between your step 6. & 7.(below), to remove any "0" values from the transposed formula, which is easy because in my particular data they necessarily occur at the end of the string.)

BELOW is the portion of your post that helped me out so effectively, & the TRANSPOSE part under step 2 was key for me :

... RE: Let´s concatenate these values:

1.Select cell B1.

2.Type =A1:A50 in formula bar.

Edit: Depending on your regional settings, try this: =TRANSPOSE(A1:A50)

3.Press F9.

4.Delete curly brackets in formula bar.

5.Type =Concatenate( in front of all characters in formula bar.

6.Type ) at the end in formula bar.

7.Press Enter

Formula in cell B1:

=CONCATENATE("A1","B1","C1","D1","E1","F1", ...) + ENTER

Connie,

Thank you!

Use Find and Replace to remove null values (CTRL + H)

Thank you so much for this.

It has helped me with a project I am working.

Such a timesaver.

It has opened my mind to so many ideas, as well.

Thanks. Well done!!!

Gregg

Gregg Harris,

Thanks for commenting!

Hi--I am not getting "" around my numbers...they are just commas...is there something Im missing?

sharon,

No, you are right. Only text values have quotation marks. But you can concatenate numbers also.

Hi,

I really liked this solution.

Was wondering how to deal with a dynamic range (e.g the initial formula was =transpose(A1:A50)..., but it may become A1:60 and I still want all values concatenated with the "," delimiter.

Thanks

Sam,

Yes you can.

Download *.xlsx file

Concatenate-a-dynamic-named-range-and-a-table.xlsx

In Column A, rows 1 to 5, type the following:

red

yellow

blue

green

orage

In cell B2:

=IF(ROW()=2,A1&OFFSET(B2,-1,0)&","&OFFSET(B2,0,-1),OFFSET(B2,-1,0)&","&OFFSET(B2,0,-1))

Drag this formula down to cell B5.

Cell B5 will now contain the string "red,yellow,blue,green,orage"

Nuovella,

Yes, I have made something similar:

http://www.get-digital-help.com/2010/08/23/concatenate-cell-values-in-excel/

Thanks Oscar.

Hi OSCAR:

Luckly found this site and wonderful.

I have a work sheet called: Data. Column A has dates and column B has related data. New data is being added weekly based at top (current at top and older downward). Then I create chart that shows me line chart of all data (last 10 years or so), up to here no problem and I am good.

Problem I have when need to create chart of latest one year data or latest 2 years data. In this case I need to change chart series reference cell. My start date always starts at A7. What I am trying to do is enter number of weeks (assuming 52 w/yr multiply by number of years) in one cell then CONCATENATE A and number of weeks to get end date. Then concatenate Start date A7 and End date (concatenated after entering number of weeks) to get a Date range e.g. A7:A59. Up to here I am doing ok (may be I am wrong). How to link this concatenated range to chart data series, so chart will show me graph of required date range.

Any help would be appreciated. Spent almost one week searching on web to find any related help but no successful.

Here is data of my table:

Date=Colum: A Data=Column B

=================================

11/03/2014 684249

04/03/2014 656664

25/02/2014 637838

18/02/2014 657075

11/02/2014 617237

04/02/2014 602247

28/01/2014 608056

21/01/2014 663764

14/01/2014 664721

07/01/2014 630115

Kam,

I have a work sheet called: Data. Column A has dates and column B has related data. New data is being added weekly based at top (current at top and older downward). Then I create chart that shows me line chart of all data (last 10 years or so), up to here no problem and I am good.Problem I have when need to create chart of latest one year data or latest 2 years data. In this case I need to change chart series reference cell. My start date always starts at A7.

I understand you up to this point. Anyway, perhaps this post is helpful: Make a dynamic chart for the most recent 12 months data

Oscar, thanks a lot for help. Found out way through your help.

Good work, keep it up.

Regards

Kam

Kam,

thank you.

Dear Oscar

I have the following entries in two adjacent columns:

ColA,ColB

a,one

a,two

b,three

b,four

c,five

c,six

I need concatenation of colB entries in colC, as follows:

C1:one,two

C3:three,four

C5:five,six

Thanks

I need array formula solution and not VBA code or UDFs.

Thanks

SridharVenu,

This is a regular formula.

I thought you could use a spare sheet to construct a concatenate command.

I wanted to join all the cells from D2 to EM2.

But how to get the x2,y2,z2,aa1,ab1, etc.

You can convert numeric references to cell IDs using "address"

for me I needed D2 which is 2,4

I had a column A which I autofilled with 4,5,..143

To get the relative address, D2 without any $s, use 4 for the address type.

type this in B2 =address(2,A1,4)

Autofill down by double clicking in the little square at the bottom right of the cell.

Now build the list.

in d1 type =B1

in d2 type =D1 & "," & B2

Drag the autofill to the bottom and you will end up with all the cell names seperated by commas in D139

in c139 type ="=concatenate("&D139&")"

This is a bit weird because we are constructing an excel formula with strings that look like excel formula! :-)

The value of C139 will now be our big concatenation command.

Copy the cell and go to C2 on the sheet where you want the concatenate.

You now have to trick excel!

Paste value into the cell.

Excel thinks "This is a value so I'l better treat it as a literal string"

the value of C2 is now "=concatenate(B2,C3...."

Copy the cell and paste it back into C2.

Hurrah! The value of C2 is now the concatenation of all the cells that you wanted.

Autofill down to do the same to all the rows.

(This only saves typing if you have more than 30 or so columns to concatenate. :-)

Hope this is of use!

Alan Gunn