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λ)
    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      May 11, 2022

      PeterBartholomew1 

      I definitively prefer your REDUCE solution. Thanks for sharing it

Resources