Hi all!
This may be a simple thing but i can't find solution to gather, in one single cell, the text in many different cell separeted by coma (without having to write a huuuuge formula like:
=concatenar(portuguese version)(A1; ";" ;B1; ";" ;B2 ";" ...etc etc)
Is there any code that can do this more simply?
Thanks in advance and greets from sunny Portugal
It's possible, but based on your minimal example data, it appears you're trying to concatenate non-contiguous data (not an entire row, column or range of cells). If that's truly the case you have to decide which will take longer:
Use the CONCATENATE formula like you mentioned originally, or
Select all of the cells you want to concatenate and define a name for that range of cells (Insert -> Name -> Define). If you do this you can then use VBA code to concatenate all of the cells in the named range. Example code below:
In this code you would change "YourNamedRange" to it's actual name, including the quotes. Also, change Range("A8") to the cell you want the results in.
Please Login or Register to view this content.
Hi! And thanks for the reply!
Sorry about the minimal data...i've inserted wrong data on the example...what i really want is contiguous data (an entire collum).
The VBA code you've sent is actually more simple to do then that huge formula i talked about in the first post(and i think this formula has a limit number of cells that you can use)
Knowing it is contiguous data? What do you advise?
Tkx again!!
If you download the Morefunc addin then you can also do this using the MCONCAT function. Get morefunc here
http://xcell05.free.fr/english/
Hi again!!
I tried the morefunc.xll and it works great, the only problem i still have(and don't think it can be solved) is that i needed a large number of words(i think tha max letters for cell is 255 right?) in the same cell or an automatic way to separate it in different cells automaticly.
The VBA code doesn't seem to be workin, knowing that the selection are more than 200 cells with an email each one...so...lot of letters, and maybe this was the problem. But i've tried with only 3 cells with a total of 66 letters only, and it didn't work anyway(at least nothing is shown on the target cell i chosed, C1)
Here's the code i've used:
Think this was going to be easy??Please Login or Register to view this content.
Tkx again
Hi, corvito. The code you posted works fine for me. Are you sure you have the named range properly referenced? Also, dumb question maybe, but you know you need to manually run that procedure, correct?
Also, I believe the max characters in a cell is 32,767, unless it is a formula, which then the max is 1,024, but not positive on that one.
Jason
errr... I'm the dumb here... I'm baddly use to everything being automaticly, well done Jason on asking about the manually run that procedure
It's working now and i'm on testing time!!
Tkx a lot everyone !!!
Greets to all from sunny Portugal (OK, not that sunny today...)
LOL! Well, at least it was a simple error. Glad I could help.
Glad my code could help you out. Thanks, Jason, for the follow-up.
Now knowing it is contiguous data you're working with, there is other code that could be used to simply parse through the rows in one column and set the cell's value rather than having to define a range first and work on that range. For example, if your e-mail data was in A1:A500, you could use something like:
But, since a single cell, a column, a row or any non-contiguous cells can be grouped as a defined range name, the code you currently have works just fine too.Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks