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 format (firstname lastname;#####) . I am searching for a way to clean the cells so only Firstname.Lastname (or Firstname.Lastname for each person). Any advice or tips would be appreciated. (side note, I don't control this particular sharepoint list, so I can't modify the view or data and I can't install any plugins or additional apps to support this functionality)

 

I have tinkered with some replace functions but because the number of names in the cell vary, I couldn't get the results needed.

 

Below is a sample of the data with the names changed:

Apps Leader
Bob Evans;#1675
Doug smith;#652
Doug smith;#652;#Joe Smith;#4736
Doug smith;#652;#Joe Smith;#4736
Doug smith;#652;#Justin Smith;#10754
Doug smith;#652;#Tina smith;#664

 

Here is what I would like it to be

Bob Evans
Doug smith
Doug smith; Joe Smith
Doug smith; Joe Smith
Doug smith; Justin Smith
Doug smith; Tina smith

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)
)

6 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 11, 2022

    islic 

    This can also be done in Excel 365 though there is some formula writing to be done.  The worksheet formula

    = SUBSTITUTE(
      REDUCE(strings,digits,Removeλ),
      ";;","; ")

    uses REDUCE to call the Lambda function 'Removeλ' for each character in 'digits',

    "Removeλ"
= LAMBDA(s,t,SUBSTITUTE(s,t,""))

"digits"
={0;1;2;3;4;5;6;7;8;9;"#"}

    • 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)
)

Resources