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),"")
    )
    );
HansVogelaar's avatar
HansVogelaar
MVP
Jul 06, 2021

mohamedsaleem18 

Copy the following function into a module in the Visual Basic Editor:

Function SplitAddress(ByVal s As String)
    Dim p As Long
    Dim v(1 To 3) As String
    If Left(s, 3) = "No " Then
        s = Mid(s, 4)
    ElseIf Left(s, 3) = "No:" Then
        s = Trim(Mid(s, 4))
    End If
    p = InStr(s, " ")
    v(1) = Left(s, p - 1)
    s = Mid(s, p + 1)
    If Len(s) <= 40 Then
        v(2) = s
    Else
        If Mid(s, 41, 1) = " " Then
            p = 41
        Else
            p = InStrRev(s, " ", 40)
        End If
        v(2) = Left(s, p - 1)
        v(3) = Mid(s, p + 1)
    End If
    SplitAddress = v
End Function

Let's say the addresses start in A2.

Select B2:D2.

Enter the following formula and if you don't have Excel in Microsoft 365, confirm it with Ctrl+Shift+Enter.

=SplitAddress(A2)

Fill down.

Save the workbook as a macro-enabled workbook (.xlsm) and make sure that you allow macros when you open it.

 

Others will probably post a solution using Lambda functions or PowerQuery.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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