Cleaning up sharepoint export

[islic]

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

[#2]

@islic Perhaps PowerQuery is the tool for the job.

Demo attached using your example.

[#3]

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!

[#4 · Accepted solution]

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

[#5]

@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;"#"}

[#6]

@L z. 

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

[#7]

@Peter Bartholomew 

I definitively prefer your REDUCE solution. Thanks for sharing it