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),"")
    )
    );
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jul 06, 2021

    HansVogelaar 

    Tried with Power Query. Not optimal, at least from performance point of view.

    with

    let
    Source = Excel.CurrentWorkbook(){[Name="range"]}[Content],
    filterEmptyRows = Table.SelectRows(Source, each ([Column1] <> null)),
    promotHeader = Table.PromoteHeaders(filterEmptyRows, [PromoteAllScalars=true]),
    removeNo = Table.AddColumn(
        promotHeader,
        "Custom",
        each
            if Text.Start(Text.Lower([Address]), 2) = "no"
            then Text.TrimStart(Text.Range([Address], 3))
            else
                if Text.Start(Text.Lower([Address]), 3) = "no:"
                then Text.TrimStart(Text.Range([Address], 4))
                else Text.TrimStart([Address])
    ),
    keepResult = Table.SelectColumns(removeNo,{"Custom"}),
    extractLeftNumber = Table.SplitColumn(
        keepResult,
        "Custom",
        Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false),
        {"Part.1", "Parts"}
    ),
    rightSpacePosition = Table.AddColumn(
        extractLeftNumber,
        "Custom",
        each Text.PositionOf(Text.Start([Parts], 40), " ", Occurrence.Last)
    ),
    first40 = Table.AddColumn(
        rightSpacePosition,
        "Part.2",
        each
            if Text.Length([Parts]) > 40
            then Text.Start([Parts],[Custom])
            else [Parts]
    ),
    after40 = Table.AddColumn(
        first40,
        "Part.3",
        each
            if Text.Length([Parts]) > 40
            then Text.Trim(Text.Range([Parts],[Custom]))
            else ""
    ),
    final = Table.SelectColumns(after40,{"Part.1", "Part.2", "Part.3"})
in
    final
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 06, 2021

      SergeiBaklan 

      Thanks, Sergei. It may be slower than the VBA function, but I hope it'll work for the OP.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jul 06, 2021

        HansVogelaar 

        That definitely could be optimized, but in any case will be slower compare to VBA. The question is only how slower.

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

    HansVogelaar Thank you for your response.  I am getting "unrecognized text in formula error".  Can you please help me to resolve this

     

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

        HansVogelaar Thank you.  It seems some configuration issue in my excel.  I have downloaded your file and tried one new row but errored out (same).  Please find the screenshot below.  Can you please help?

         

Resources