Combine data in two cell into one

[qiyusi]

Hello,

Similar thread match with my problem should be solved on somewhere in the forum.
But i can't find it.

I want to combine two cells single cell, it's like permutation/combination i am not sure.
example:

cell1 cell2 cell3

A 1 A1
B 1,2 B1,B2

Is it any efficient formula? i am think using subtitute function,

=CELL1&SUBSTITUTE(CELL2,",",","&CELL1) but not work well for data more than one in cell1 and cell2

please check attached file.

Thank you.

[liquidmettle]

Hello Qiyusi,

Even without viewing your file, have you tried the Concatenate function?

Please Login or Register  to view this content.

You can even put words not in the cells or spaces between cells by placing them within quotes (see below):

Please Login or Register  to view this content.

If a1 = Isn't this

B1= This will likely solve

C1= Your problem

With that code it would read:

Isn't this Nifty Eh? This will likely solve Your problem


Also, you can write a large number of formulas this way if you use concatenate to combine the pieces, then copy and Paste Special> Paste Values. This will take the result of the concatenate formula and make it plain text (no longer in a formula).

[Crooza]

here's a macro solution if you're open to macros

Please Login or Register  to view this content.

[qiyusi]

@liquidmettle: Thank you for your reply, how ever this is not simple concatenate problem.

@Crooza: Perfect!!! exactly what i need. And yes, i open to macro solution.

Thank you!

[qiyusi]

Crooza,

Just one last question,

is it possible to add space [ ] between combine data: example B[space]1,B[space]2 rather than B1,B2?

[Crooza]

I haven't tested this but try changing this line

finalstring = finalstring & list1(i) & list2(j) & "," to this

finalstring = finalstring & list1(i) & " " & list2(j) & ","

Let me know if this isn't what you want and I'll look at it again

[qiyusi]

Great! it works like a charm