Forum Discussion

mohamedsaleem18's avatar
mohamedsaleem18
Copper Contributor
Jul 06, 2021
Solved

Text (address) formatting in Excel

I'm new to Excel text formatting.  Can you please help me to do this below text formatting Requirement:  Address column is a free text.  This should be split in to three column.   1st column shou...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jul 06, 2021

    HansVogelaar 

    Another mockup with lambdas

    prefix = {"No", "No:"};
removeFirst = lambda(str, txt, SUBSTITUTE(str, txt, "",1) );

////
noPrefix = lambda(str, k,
    IF(k=0, TRIM(str),
        LET(t, removeFirst(str, INDEX(prefix,1,k)),
            noPrefix(t, k-1) )
    ));
cleanText = lambda(str, noPrefix(str, COLUMNS(prefix)));
nFirstSpace = lambda( str, FIND(" ", str)-1);
textStart = lambda(str, n, TRIM(LEFT(str, n)));
textEnd = lambda(str, n, TRIM(RIGHT(str, LEN(str)-n)));
lastPos = lambda(str, chr, n,
    IF(RIGHT(str)=chr, n,
     lastPos(LEFT(str, n-1), chr, n-1)));
posRightSpace = lambda(str, lastPos(str, " ", LEN(str)));

////
splitParts = lambda( str,
    LET(
        txt, cleanText(str),
        nA, nFirstSpace(txt),
        partA, textStart(txt, nA),
        partBC, textEnd(txt, nA),
        first40, textStart(partBC, 40),
        space40, posRightSpace(first40), 
        partB, IF(LEN(partBC)<=40,
                partBC,
                textStart(partBC, space40)
        ),
        partC, IF(LEN(partBC)<=40,
                "",
                textEnd(partBC, space40)
        ),
        IFERROR(CHOOSE({1,2,3}, partA, partB, partC),"")
    )
    );

Resources