Combine every 10 cells into another cell then…

Grand

Board Regular
Joined
May 11, 2017
Messages
52
Hi everybody, new here.
I need a macro that takes e.g. every 10 cells from let’s say column A (the length of column A varies from time to time so I don’t have a fix end) and combine the characters from those cells into one string which is comma separated and paste that into a single cell. Ideally the width of the receiving cell/column then should be a bit wider than standard like ~ 100. The width is not so important if that is complicated to do.
Here an example to illustrate:
A
CD
1224
Chunk 1:
1224, 2345, 2345, 3452
2345
Chunk 2:
5634, 1224, 2345, 2435
2345
Chunk 3:
and so on
3452
Chunk 4:
and so on
5634
so on
1224
2345
2435
3452
5634
1224
2345
2345
3452
5634
1224
2345
2345
3452
5634
end unknown

<tbody>
</tbody>

Any help is appreciated.
Thanks.
 
Re: How to combine every 10 cells into another cell then…

This does the work for my original post too.
Thanks :)
 
Upvote 0

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Re: How to combine every 10 cells into another cell then…

Thanks Sektor,
you all been helping so fast great.
Thank you again.
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

Thank you, except this here it works nicely:
Formula = "= ""Chunk: "" & ROW()"

but that is a minor thing.
Thanks gain.
You are welcome.

There should be a dot (.) before "Formula". Maybe that's why it doesn't work for you. Can you tell me what exactly "doesn't work"?
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

Just wondering... does your data have spaces within the individual cells? If not, have you seen the code I posted in Message #9?
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

You are welcome.

There should be a dot (.) before "Formula". Maybe that's why it doesn't work for you. Can you tell me what exactly "doesn't work"?

it is jsut that it shows this "Formula = "= ""Chunk: "" & ROW()"" instead of e.g. "Chunk:1".
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

Just wondering... does your data have spaces within the individual cells? If not, have you seen the code I posted in Message #9?

Me in post #11 was actually thanking you Rick.

No, every cell contains a combination of three or four letters and six numbers. I tested your code too and it works very fine :) <o:p abp="1791"></o:p>

However, as I have this additional requirement (which Isadly “forgot” to mention in my original request) that the joined string shall not exceed 255 characters length (including commas and spaces), I am using JoeMo's code (which does taht 255 thing); unless you can provide some magic? <o:p abp="1795"></o:p>

To top that challenge, the code should take the values from the column with the specific column name (column name is placed in row 3) as the data comes in different column every time but the title is the same “Batch”.
 
Last edited:
Upvote 0
Re: How to combine every 10 cells into another cell then…

it is jsut that it shows this "Formula = "= ""Chunk: "" & ROW()"" instead of e.g. "Chunk:1".
You may have that column or some of its cells formatted as text. Try formatting that column as General before you run the code.
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

No, every cell contains a combination of three or four letters and six numbers. I tested your code too and it works very fine :) <o:p abp="1760"></o:p>
However, as I have this additional requirement (which I sadly “forgot” to mention in my original request) that the joined string shall not exceed 255 characters length (including commas and spaces), I am using JoeMo's code (which does taht 255 thing); unless you can provide some magic?
I presume you mean each combination of 10 cells when concatenated should not exceed 255 characters, correct? Well, if your cells really only contain a maximum of 4 letters plus and additional 6 digits, the total number of characters in a single cell is a maximum of 10... so that 10 cells of 12 characters (10 characters in the cell plus the comma space between it and the next cell's data) is only 120 total characters maximum, nowhere near the 255 limit you mentioned. What have I misunderstood in that you thought it necessary to mention the 255 character limit?
 
Upvote 0
Re: How to combine every 10 cells into another cell then…

I presume you mean each combination of 10 cells when concatenated should not exceed 255 characters, correct? Well, if your cells really only contain a maximum of 4 letters plus and additional 6 digits, the total number of characters in a single cell is a maximum of 10... so that 10 cells of 12 characters (10 characters in the cell plus the comma space between it and the next cell's data) is only 120 total characters maximum, nowhere near the 255 limit you mentioned. What have I misunderstood in that you thought it necessary to mention the 255 character limit?

You are right with the math. However, that 10 cells thing was just to start the case :) My plan was later to increase the number of cells to be concatenated of course.
I just realized that actually it is not important how many cells to take each time but what more important in my case is the length of the combined string which should not exceed 255 characters while at the same time maintaining the integrity of the original numbers.
255 requirement is due to the fact that I need that string to be inserted into a database which it only accepts 255 char long strings. Am I making sense? <o:p abp="1938"></o:p>
 
Last edited:
Upvote 0
Re: How to combine every 10 cells into another cell then…

You may have that column or some of its cells formatted as text. Try formatting that column as General before you run the code.
Thanks for the tip, I will try it :)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top