concatenate text alternatively from 2 or more cells

mariusliviu

New Member
Joined
Jun 20, 2023
Messages
7
Office Version
  1. 365
  2. 2021
  3. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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):
1687298956760.png


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
 
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.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
How about this?

Book1
ABCD
5a b cd ea|d b|e c|
Sheet1
Cell Formulas
RangeFormula
D5D5=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)))
 
Upvote 0
1687419436437.png
1687419500425.png
or
1687419539809.png

How about this?

Book1
ABCD
5a b cd ea|d b|e c|
Sheet1
Cell Formulas
RangeFormula
D5D5=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.
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top