concatenate text alternatively from 2 or more cells

[mariusliviu]

Hello, I hope this wasn't asked before, I've tried to search multiple ways...
Anyway, is it possible to create a formula or a VBA code that can do this (cells A and B have text, cell C should be the result):

The text in cells A and B have a line break (there are 3 rows but it can be more or less). In cell C it's done a concatenation of each line in the cell with a delimiter "|" between text in cell A and text in cell B and then a row break (char(10)) between each line in cell. The text could be only text or numbers, or symbols or a phrase...
I think it's clear enough from the image.
Is it possible to do with a formula or a macro (I prefer formula if it's easier)?

Thank you,
Marius

 

[jolivanes]

Upload a Mini-Sheet instead of a picture.

You mention "two or more cells". If it is three, the result in Column C is going to be a little crowded.

 

[lrobbo314]

How about this?

marius
	A	B	C	D
1	andrwasd dasd 13as1322 fasd 4432f	cria123sti asd 123asd1 dsd 4141 asdff12 asda 4331 3112sda		andrwasd dasd|cria123sti asd 123asd1 dsd 13as1322 fasd|4141 asdff12 asda 4432f|4331 3112sda
Sheet9

Cell Formulas
Range	Formula
D1	D1	=LET(a,TEXTSPLIT(A1,,CHAR(10)),b,TEXTSPLIT(B1,,CHAR(10)),TEXTJOIN(CHAR(10),,a&"|"&b))

 

[jolivanes]

If the two are separated by a linefeed, this should work.

Sub AAAAA()
Dim c As Range, spl_A, spl_B
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
spl_A = Split(c, Chr(10))
spl_B = Split(c.Offset(, 1), Chr(10))
    With c.Offset(, 2)
        .Value = spl_A(0) & " | " & spl_B(0) & Chr(10) & spl_A(1) & " | " & spl_B(1) & Chr(10) & spl_A(2) & " | " & spl_B(2)
        .WrapText = True
    End With
Erase spl_A
Erase spl_B
Next c
End Sub

 

[lrobbo314]

This update will allow you to use as many cells as you want.

marius
	A	B	C	D
1	a b c	d e f	g h i	a|d|g b|e|h c|f|i
Sheet9

Cell Formulas
Range	Formula
D1	D1	=TEXTJOIN(CHAR(10),,LET(r,REDUCE("",A1:C1,LAMBDA(s,c,s&TEXTSPLIT(c,,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

 

[mariusliviu]

If the two are separated by a linefeed, this should work.

Sub AAAAA()
Dim c As Range, spl_A, spl_B
For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
spl_A = Split(c, Chr(10))
spl_B = Split(c.Offset(, 1), Chr(10))
    With c.Offset(, 2)
        .Value = spl_A(0) & " | " & spl_B(0) & Chr(10) & spl_A(1) & " | " & spl_B(1) & Chr(10) & spl_A(2) & " | " & spl_B(2)
        .WrapText = True
    End With
Erase spl_A
Erase spl_B
Next c
End Sub

thank you for your solution, I think it works, I didn't try because I see that somebody else found an easier solution (for me) with a formula.

 

[mariusliviu]

This update will allow you to use as many cells as you want.

marius
	A	B	C	D
1	a b c	d e f	g h i	a|d|g b|e|h c|f|i
Sheet9

Cell Formulas
Range	Formula
D1	D1	=TEXTJOIN(CHAR(10),,LET(r,REDUCE("",A1:C1,LAMBDA(s,c,s&TEXTSPLIT(c,,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

this is awesome, I thought that it was a way to do, but I didn't know how.
in case it's a value that is missing for example:

in cell B there are only 2 rows, not 3 like in A.
it can be modified that formula? I really don't understand how those formulas work, I never tried LET, REDUCE and LAMBDA.
sorry but I can't install add-ons (xls2bb).

 

[Tom.Jones]

Use same formula, offered from lrobbo314, only modify the range:
=TEXTJOIN(CHAR(10),,LET(r,REDUCE("",A1:B1,LAMBDA(s,c,s&TEXTSPLIT(c,,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

 

[mariusliviu]

Use same formula, offered from lrobbo314, only modify the range:
=TEXTJOIN(CHAR(10),,LET(r,REDUCE("",A1:B1,LAMBDA(s,c,s&TEXTSPLIT(c,,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

that's not what I was referring
in case that in cell B1 there are only 2 rows (instead of 3 in this example), the result will be #N/A and if there is a space, it will insert one more "|".

 

[Tom.Jones]

Space is a character.