Help with concatenate formula

[Jveto]

Hello everyone,

I am looking for help simplifying concatenate.

What I want to do is take a bunch of cells and combine them into one cell. The problem with using concatenate, is it's a lot of cells and I've run out of room before.

I want to combine all the contents from A1 to A30 into just B1. How would I do that? If possible I'd like to put a comma in between each entry.

This

C1

C2

C3

C4

C5

C6

C7

C8

C9

C10

into this

C1, C2, C3, C4, C5, C6, C7, C8, C9, C10

[José Augusto]

Hi
If data is in A2:A11 column use this formula

Please Login or Register  to view this content.

and copy down

[FDibbins]

using formulas, there is no real easy way to do this. There is a way to use VBA for this, but I cannot find the link

For the formula, try this...
=SUBSTITUTE(TRIM(A1&" "&A2&" "&A3&" "&A4&" "&A5&" "&A6&" "&A7&" "&A8&" "&A9&" "&A10)," ",",")

Another option, if you are OK using a helper column (which you can hide if you want) would be something like this...

	A	B
1
2	C1	C1
3	C2	C1,C2
4	C3	C1,C2,C3
5	C4	C1,C2,C3,C4
6	C5	C1,C2,C3,C4,C5
7	C6	C1,C2,C3,C4,C5,C6
8	C7	C1,C2,C3,C4,C5,C6,C7
9	C8	C1,C2,C3,C4,C5,C6,C7,C8
10	C9	C1,C2,C3,C4,C5,C6,C7,C8,C9
11	C10	C1,C2,C3,C4,C5,C6,C7,C8,C9,C10

B2=SUBSTITUTE(TRIM(B1&" "&A2)," ",", ")
copied down

[FDibbins]

Hi
If data is in A2:A11 column use this formula

Please Login or Register  to view this content.

and copy down

Unless Im missing something, all that gives me is this?

	C
1	C1,
2	C2,
3	C3,
4	C4,
5	C5,
6	C6,
7	C7,
8	C8,
9	C9,
10	C10
11

[José Augusto]

Hi FDibbins

Sorry!
formula is

Please Login or Register  to view this content.

where B1 is empty

[Jveto]

@FDibbins - For your second reply, I got the same result.

For your first reply : That is similar to what I was doing. My problem with that was it really didn't make my process any simpler, as it takes so long to do. I have 500 lines (broken into groups between 3-50) that I am trying to reorganize. When I was doing this before, I had so many cells to combine, I actually ran out of room for typing in my formula.

However you second method, with the helper column, could actually work. It's a little bit of work, and some cleaning up to do afterwards, but that might just do it.

I guess it doesn't need to be a formula, just a method to do it. I was hoping I was missing some simple excel copy/paste command where you could paste into one cell instead of spreading it out.

Edit: With Jose's added comment, that actually works, accomplishes the same thing FDibbins has, just less typing.

[Jveto]

I might be using this method. Can you explain what this formula means?

I'm reading it as : This cell = The cell above, plus the one to the left of this cell. (=B1&A2) I get confused at the & IF(A3<>"",", ","") part.

Thanks for the help.

[FDibbins]

Jose's formula is doing pretty much the same as my 2nd suggestion (with the helper) is doing...without the SUBSTITUTE/TRIM bits to remove the trailing comma

[José Augusto]

Hi
the IF statement is for comma in front C1, C2, ... but not in front C10,
search this forum for "Concatenate Function Modification"
Regards