Concatenate all cells in a row to the last cell with data.

[jeff p]

I'm in need of a method to concatenate each row from B1 to the last cell with data on the same row. ( it varies)
The maximum range I might need would be B1:CD1
I'm unable to add each cell within the function, as its too large.

I have about 100 rows like this.

Any ideas?

Thanks!

[mehmetcik]

a user defined function maybe?

Please Login or Register  to view this content.

[sktneer]

This can easily be achieve with the help of a VBA code. But as far as the formula is concerned, you need several steps to achieve this. Lets take a simple and small example of this.
Lets say you have values in the range B1:K1 from 1 to 10.

Step 1. Type the following formula in the formula cell but don't press Enter.

Please Login or Register  to view this content.

Step 2. Now without pressing Enter, click in the formula bar and select the red colored range in the formula bar

Please Login or Register  to view this content.

Step 3. After selecting the red range, press F9 (Function Key) and your formula will become like this in the formula bar.

Please Login or Register  to view this content.

Step 4. Now delete the curly brackets (shown above in red)from the formula in the formula bar and your formula will look like this...

Please Login or Register  to view this content.

Step 5. Now press Enter to confirm the formula and you will get the following concatenated string in the formula cell.

Please Login or Register  to view this content.

If you want to use , (comma) as a separator, you may start with the following formula in step 1.

Please Login or Register  to view this content.

and select everything inside the brackets (shown below) before going to the step 3.

Please Login or Register  to view this content.

You may also use the key combination Ctrl + Shift + Right Arrow Key to select the data in a row i.e. select B1 and then use this key combination to select last cell with data in row1.

Does this help?

[mehmetcik]

Following on from sktneer's suggestions

this version accepts an optional text deliminator

Please Login or Register  to view this content.

[jeff p]

Thanks, sktneer

That's an interesting way to get the same result. Although I'm not sure if this method will work if my data is continuously being updated.


mehmetcik, Your code worked great!! ( and thanks for the included xlsm file!)


Thanks guys!

Cheers