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

 

[mariusliviu]

Space is a character.

I think I will leave it like this. Overall is what I wanted in the first place. There will be some individual cases on some cell but it's ok like this.
Thank you for your time and solution.

 

[lrobbo314]

I am looking at it., but it's not immediately clear how do it with formulas.

It can be done with PowerQuery as well. Or, if like Tom was saying, you can go into the cells that are missing a row and just insert a space character and it should work as expected.

 

[lrobbo314]

How about this?

Book1
	A	B	C	D
5	a b c	d e		a|d b|e c|
Sheet1

Cell Formulas
Range	Formula
D5	D5	=TEXTJOIN(CHAR(10),,LET(crc,LAMBDA(cel,LEN(cel)-LEN(SUBSTITUTE(cel,CHAR(10),""))+1),mx,MAX(SCAN(0,A5:B5,LAMBDA(s,c,crc(c)))),r,REDUCE("",A5:B5,LAMBDA(s,c,s&TEXTSPLIT(c&REPT(CHAR(10),mx-crc(c)),,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

 

[mariusliviu]

or

How about this?

Book1
	A	B	C	D
5	a b c	d e		a|d b|e c|
Sheet1

Cell Formulas
Range	Formula
D5	D5	=TEXTJOIN(CHAR(10),,LET(crc,LAMBDA(cel,LEN(cel)-LEN(SUBSTITUTE(cel,CHAR(10),""))+1),mx,MAX(SCAN(0,A5:B5,LAMBDA(s,c,crc(c)))),r,REDUCE("",A5:B5,LAMBDA(s,c,s&TEXTSPLIT(c&REPT(CHAR(10),mx-crc(c)),,CHAR(10))&"|")),LEFT(r,LEN(r)-1)))

the result should be on the same line like in the pictures.

 

[mariusliviu]

View attachment 94040View attachment 94041or View attachment 94042

the result should be on the same line like in the pictures.

I can't edit the message.
sorry, I didn't check the formula and I answered based on your example.
I've tried your formula on excel now and it shows:

so, it should be like in the picture (without those symbols "|" if it's empty):

so, if you want to try if it's possible, no problem