Concat Values In Same Column

[CyberMitch]

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.

[#2]

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))

[#3]

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.

[#4]

@CyberMitch 

=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.

 

[#5]

@CyberMitch 

This is a 365 solution.

= LET(
    Join, LAMBDA(x, TEXTJOIN("|",,x)),
    BYROW(WRAPROWS(input,2), Join)
  )