Forum Discussion

Amanda_P77's avatar
Amanda_P77
Copper Contributor
Oct 14, 2022

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

Formulas and Functions
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Oct 15, 2022

    Amanda_P77 

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

     

Resources