Text (address) formatting in Excel

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),"")
    )
    );