Oct 14 2022 07:37 AM
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 following:
=TEXTJOIN("/", TRUE, Table134[@PROJECT]:Table134[@[Unit (Block)]:[Column1]])
How do I hold the text contents of the cell? Holding control key while selecting cells to combine did not work...
Thanks for any help!
Oct 14 2022 09:45 PM
Assuming the idea is to separate Alpha. & Digits in each cell, then to concat. all:
With MSFT 365, in G4 - formula Spills as a Dynamic Array:
=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))
)
)
)