Shorten my CONCATENATE formula

[lordfa9]

I have the following:

=CONCATENATE(B3,C3,D3,E3,F3,g3,h3,i3,j3,k3,l3,m3,n3,o3,p3,q3,r3,s3,t3,u3,v3,w3,x3,y3,z3,aa3,ab3,ac3,ad3,ae3,af3,ag3,ah3,ai3,aj3,ak3,al3,am3,an3,ao3,ap3,aq3,ar3,as3,at3)

is there any way to shorten it?

[Glenn Kennedy]

There will be VBA ways to do this properly. This sort-of achieves the same result. But it's not very useful (as you'll see).


Select any empty cell.
Type =concatenate(TRANSPOSE(b3:at3))
Witht hte mose, select the TRANSPOSE(H3:AT3) portion.
Press F9.
Delete curly brackets in formula bar.
Press Enter

[lordfa9]

you mean the curly brackets at the start and end of the resulting long string of text right? Did that and while I could see a lot of stuff in the formula bar, the actual cell was empty

[Glenn Kennedy]

Yes. when you hit F9, was the "stuff" separated by commas or semicolons?

[lordfa9]

yup. The stuff also appeared to be the right answer (for that particular line) but it didn't appear in the spreadsheet itself

[Glenn Kennedy]

Which... commas or semicolons?

[Norie]

Perhaps you could do a copy/paste special values on the cell containing the formula Glenn suggested.

[Vikas_Gautam]

If your are on for VBA code.
IT WILL HANDLE EMPTY CELLS AS WELL
Syntax:- JoinC(Range OR Array, Dilimiter as String)
Here is the formula

Please Login or Register  to view this content.

Here are the Codes:-

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Check the attached file

[lordfa9]

it became:

={"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM,";"";"";"EXCELFORUM,";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

when I removed the curly brackets highlighted above in red, excel refused to accept the output

[lordfa9]

Perhaps you could do a copy/paste special values on the cell containing the formula Glenn suggested.

didn't work, cell really became blank

[Glenn Kennedy]

Here are a few options that you may not have seen:
http://www.get-digital-help.com/2011...-vba-in-excel/
http://www.cpearson.com/excel/stringconcatenation.aspx
http://excel.tips.net/T003062_Concat..._of_Cells.html

there is always a "brute force & ignorance" approach...

[Vikas_Gautam]

A correction to the Glenn's technique.
Try this.

Please Login or Register  to view this content.

Type above formula and Select the red portion..
Press F9 and Remove the"{}" braces
Press enter.
It will work definitely.

[Glenn Kennedy]

Vikas, it does work.... at least on my PC. I suspect that regional settings were messing up my previous efforts...

[lordfa9]

A correction to the Glenn's technique.
Try this.

Please Login or Register  to view this content.

Type above formula and Select the red portion..
Press F9 and Remove the"{}" braces
Press enter.
It will work definitely.

It works but now it literally pastes the following into my cell:

"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM,";"";"";"EXCELFORUM,";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""

Now I have to manually remove the quotation marks and semicolons, also I have to do your steps for every single row -_-

[:) Sixthsense :)]

Witht hte mose, select the TRANSPOSE(H3:AT3) portion.Press F9.

Till now not aware of this evaluation part inside the formula using F9. Learned a new thing today and thanks for that

[Vikas_Gautam]

Now I have to manually remove the quotation marks and semicolons, also I have to do your steps for every single row -_-

Thats why I suggest you to use the UDF in Post #8, as its much more simpler and faster..

[lordfa9]

Here are a few options that you may not have seen:
http://www.get-digital-help.com/2011...-vba-in-excel/
http://www.cpearson.com/excel/stringconcatenation.aspx
http://excel.tips.net/T003062_Concat..._of_Cells.html

there is always a "brute force & ignorance" approach...

none of the links were really helpful (the VBA made my head spin lol) but I'll just stick to using the old CTRL+Lclick method (if there aren't too many (<20) columns or the method you showed me in the excel file if there are a LOT of them

[lordfa9]

Came up with a workaround

1) Determine which cells to concatenate (in this example A2:Y2)
2) Copy paste this formula into a cell cell in the same column as the starting cell; in this case the starting cell is Cell A2 so I inserted a row and put the formula into cell A1
=CONCATENATE(LEFT(ADDRESS(1,COLUMN(),2),1+(COLUMN()>26)),2,",")
3) Drag the formula across, so Cell A1 will have A2, Cell B1 will have B2 and so on
4) Type =Transpose(A1:Y1) into any blank cell
5) Without pressing enter, Select the range (in this case A1:Y1), it should turn blue, then press F9
6) Carefully copy anything in between the resulting curly brackets, it should look something like this:
A2;"B2,";"C2,";"D2,";"E2,";"F2,";"G2,";"H2,";"I2,";"J2,";"K2,";"L2,";"M2,";"N2,";"O2,";"P2,";"Q2,";"R2,";"S2,";"T2,";"U2,";"V2,";"W2,";"X2,";"Y2,"
7) Paste it somewhere else and use the find and replace function to remove the semi colons and open inverted commas
8) Use the output for your concatenate formula

it is in no way time saving, but it is likely human error free and works the best when there are A LOT of cells to concatenate