Forum Discussion
Concat Values In Same Column
Afternoon Excel Community,
I'm currently trying to concat two cells in the same column (It's like this due to an output from a software where use). The column is formatted to where the cell below and above need to be combined. So here is the formula I'm trying to use: =Concat(=CONCAT(B1, "|", B2). The issue with this is when dragged down it turns into this =CONCAT(B2, "|", B3) when I would like it to be like this =CONCAT(B3, "|", B4), next cell after would be =CONCAT(B5, "|", B6), and so on.
Is there a solution to this that anyone can help me with?
Example of the problem I'm facing. I would like for column d to go d1=1|2, d2=3|4, d3=5|6.
The current workaround is to just copy a paste instead of drag. Notice how D1, D3 , and D5 give me the output I would like. Then I remove the the empty spaces.
4 Replies
This is a 365 solution.
= LET( Join, LAMBDA(x, TEXTJOIN("|",,x)), BYROW(WRAPROWS(input,2), Join) )- This has been slightly solved via another work around.
Highlighting the D1 and D2 and then using the fill handle makes it to where I can drag instead of copy and pasting.
I did try something using OFFSET but I couldn't get it to work: =CONCAT(OFFSET($B$1,*2,0), "|", OFFSET($B$2,*2,0))=CONCATENATE(INDEX(B:B,ROW($C$1)+(ROW(C1)-ROW($C$1))*2),"|",INDEX(B:B,ROW($C$2)+(ROW(C1)-ROW($C$1))*2))An alternative could be this formula.
- I actually got this concat to work using row nested within it.
=CONCAT(OFFSET($B$1,(ROW(B1)-1)*2,0), " | ", OFFSET($B$2,(ROW(B1)-1)*2,0))
Thank you for being my rubber ducky discussion board.
