Forum Discussion
combine columns
I am trying to combine three columns into one with a new format. frv08 023 0020 with resulting format frv/08/023/0020 I have tried text join, ampersand, and concat, but get the fol...
Assuming the idea is to separate Alpha. & Digits in each cell, then to concat. all:
With MSFT 365, in G4 - formula https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-89a1-87a7eb36e531
=LET(
SplitAlphaNum, LAMBDA(str,
LET(
pos, COUNT(SEARCH(CHAR(SEQUENCE(26,,97)),LOWER(str))),
IF(pos = 0, str, TEXTJOIN("/",,LEFT(str,pos),MID(str,pos+1,LEN(str)-pos)))
)
),
MAP(Table134[PROJECT],Table134[Unit (Block)],Table134[Column1],
LAMBDA(Proj,Block,Comm,
TEXTJOIN("/",, SplitAlphaNum(Proj), SplitAlphaNum(Block), SplitAlphaNum(Comm))
)
)
)
I'll try it, thanks!