Cleaning up sharepoint export

New Contributor

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

6 Replies

@islic Perhaps PowerQuery is the tool for the job.


Demo attached using your example.


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!
best response confirmed by islic (New Contributor)



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



in D3:

    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,";;","; "),


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

      ";;","; ")

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

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



@L z. 

Playing with your solution, I defined a Lambda function

Cleanedλ = LAMBDA(str,
        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λ)

@Peter Bartholomew 

I definitively prefer your REDUCE solution. Thanks for sharing it