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?
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?
Last edited by lordfa9; 12-31-2014 at 12:40 AM.
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
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
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
Yes. when you hit F9, was the "stuff" separated by commas or semicolons?
yup. The stuff also appeared to be the right answer (for that particular line) but it didn't appear in the spreadsheet itself
Which... commas or semicolons?
Perhaps you could do a copy/paste special values on the cell containing the formula Glenn suggested.
If posting code please use code tags, see here.
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
Formula:Please Login or Register to view this content.
Here are the Codes:-
Please Login or Register to view this content.
Check the attached filePlease Login or Register to view this content.
Last edited by Vikas_Gautam; 12-26-2014 at 06:15 AM.
Regards,
Vikas Gautam
Excel-buzz.blogspot.com
Excel is not a matter of Experience, its a matter of Application.
Say Thanks, Click * Add Reputation
it became:
={"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM";"";"";"";"";"";"";"";"";"";"";"";"EXCELFORUM,";"";"";"EXCELFORUM,";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}
when I removed the curly brackets highlighted above in red, excel refused to accept the output
Last edited by lordfa9; 12-24-2014 at 04:06 AM.
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...
A correction to the Glenn's technique.
Try this.
Formula: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.
Vikas, it does work.... at least on my PC. I suspect that regional settings were messing up my previous efforts...
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 -_-
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thats why I suggest you to use the UDF in Post #8, as its much more simpler and faster..Now I have to manually remove the quotation marks and semicolons, also I have to do your steps for every single row -_-
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks