combine columns

New Contributor

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!

2 Replies

@Amanda_P77 

Assuming the idea is to separate Alpha. & Digits in each cell, then to concat. all:

_Screenshot.png

 

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

 

I'll try it, thanks!