google-sheets

google sheet merge two column into one column with prefix words

I'm using Google Sheets and looking for an arrayformula()? that able to merge two column into one .

let's say I have the following two columns:

A B
02543980 420000B4
02543994 420000B4
025439B0 E8079F1A

I'm hoping to write a formula to make a list like this, where the two columns are combined, and then repeat the lists infinitely (drag down):

C
ADR $02543980
OVERWRITE 42 00 00 B4
ADR $02543994
OVERWRITE 42 00 00 B4
ADR $025439B0
OVERWRITE E8 07 9F 1A

So far I use REGEXREPLACE(B2,".{2}", "$0 ") to split B column and use FLATTEN() and CONCATENATE() to get C column style .

Just wondering if there is efficient way to get C column.

Thx advance

Solution

  • You may try-

    =TOCOL({INDEX("ADR $"&TOCOL(A1:A,1)),INDEX("OVERWRITE " & TOCOL(B1:B,1))},1)

    If you need formatted text with space after two character then could try-

    =TOCOL({INDEX("ADR $"&TOCOL(A1:A,1)),INDEX("OVERWRITE " & INDEX(REGEXREPLACE(TOCOL(B1:B,1),".{2}", "$0 ")))},1)

    Another option using MAP().

    =TOCOL(MAP(TOCOL(A1:A,1),TOCOL(B1:B,1),LAMBDA(a,b,{"ADR $" & a,"OVERWRITE " & REGEXREPLACE(b,".{2}", "$0 ")})),1)

    enter image description here