Forum Discussion

islic's avatar
islic
Copper Contributor
May 11, 2022
Solved

Cleaning up sharepoint export

I have an export from a SP list, the export contains a few columns with Firstname Lastname;# and 4 random numbers all in 1 cell, or there maybes several names in the cell, but they all follow that fo...
Formulas and Functions
Macros and VBA
Office Scripts
  • Lorenzo's avatar
    Lorenzo
    May 11, 2022

    islic 

     

    Power Query should definitively work (another option in the attached file). Alternative assuming you run Excel 2021 or 365:

     

    in D3:

    =LET(
    replaced,  SUBSTITUTE(B3,";#",";"),
    remove,    TEXT(SEQUENCE(10,,0),"0"),
    split,     MID(replaced,SEQUENCE(LEN(replaced)),1),
    filtered,  FILTER(split,ISNA(XMATCH(split,remove))),
    concat,    TEXTJOIN("",,filtered),
    cleaned,   SUBSTITUTE(concat,";;","; "),
    LEFT(cleaned,LEN(cleaned)-1)
)
  • islic's avatar
    islic
    Copper Contributor
    May 11, 2022
    Thank you for responding, that looks like a possible answer. I have tried a few attempts and haven't been able to duplicate. I keep running into "unexpected errors". I don't really have any experience with PowerQuery but I will keep tinkering and maybe in the meantime, someone will have another idea. Thanks Riny!
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 11, 2022

      islic 

       

      Power Query should definitively work (another option in the attached file). Alternative assuming you run Excel 2021 or 365:

       

      in D3:

      =LET(
    replaced,  SUBSTITUTE(B3,";#",";"),
    remove,    TEXT(SEQUENCE(10,,0),"0"),
    split,     MID(replaced,SEQUENCE(LEN(replaced)),1),
    filtered,  FILTER(split,ISNA(XMATCH(split,remove))),
    concat,    TEXTJOIN("",,filtered),
    cleaned,   SUBSTITUTE(concat,";;","; "),
    LEFT(cleaned,LEN(cleaned)-1)
)
      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        May 11, 2022

        Lorenzo 

        Playing with your solution, I defined a Lambda function

        Cleanedλ = LAMBDA(str,
    LET(
        replaced, SUBSTITUTE(str, ";#", ";"),
        remove,   TEXT(SEQUENCE(10, , 0), "0"),
        split,    MID(replaced, SEQUENCE(LEN(replaced)), 1),
        filtered, FILTER(split, ISNA(XMATCH(split, remove))),
        concat,   TEXTJOIN("", , filtered),
        cleaned,  SUBSTITUTE(concat, ";;", "; "),
        LEFT(cleaned, LEN(cleaned) - 1)
    )
);

        so the column of results is generated by

        = MAP(strings, Cleanedλ)

Resources