SOLVED

Cleaning up sharepoint export

%3CLINGO-SUB%20id%3D%22lingo-sub-3356499%22%20slang%3D%22en-US%22%3ECleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356499%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20an%20export%20from%20a%20SP%20list%2C%20the%20export%20contains%20a%20few%20columns%20with%20Firstname%20Lastname%3B%23%20and%204%20random%20numbers%20all%20in%201%20cell%2C%20%3CSTRONG%3Eor%3C%2FSTRONG%3E%20there%20maybes%26nbsp%3Bseveral%20names%20in%20the%20cell%2C%20but%20they%20all%20follow%20that%20format%20(firstname%20lastname%3B%23%23%23%23%23)%20.%20I%20am%20searching%20for%20a%20way%20to%20clean%20the%20cells%20so%20only%20Firstname.Lastname%20(or%20Firstname.Lastname%20for%20each%20person).%20Any%20advice%20or%20tips%20would%20be%20appreciated.%20(side%20note%2C%20I%20don't%20control%20this%20particular%20sharepoint%20list%2C%20so%20I%20can't%20modify%20the%20view%20or%20data%20and%20I%20can't%20install%20any%20plugins%20or%20additional%20apps%20to%20support%20this%20functionality)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20have%20tinkered%20with%20some%20replace%20functions%20but%20because%20the%20number%20of%20names%20in%20the%20cell%20vary%2C%20I%20couldn't%20get%20the%20results%20needed.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EBelow%20is%20a%20sample%20of%20the%20data%20with%20the%20names%20changed%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EApps%20Leader%3CBR%20%2F%3EBob%20Evans%3B%231675%3CBR%20%2F%3EDoug%20smith%3B%23652%3CBR%20%2F%3EDoug%20smith%3B%23652%3B%23Joe%20Smith%3B%234736%3CBR%20%2F%3EDoug%20smith%3B%23652%3B%23Joe%20Smith%3B%234736%3CBR%20%2F%3EDoug%20smith%3B%23652%3B%23Justin%20Smith%3B%2310754%3CBR%20%2F%3EDoug%20smith%3B%23652%3B%23Tina%20smith%3B%23664%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E%3CU%3EHere%20is%20what%20I%20would%20like%20it%20to%20be%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CSTRONG%3E%3CU%3EBob%20Evans%3CBR%20%2F%3EDoug%20smith%3CBR%20%2F%3EDoug%20smith%3B%20Joe%20Smith%3CBR%20%2F%3EDoug%20smith%3B%20Joe%20Smith%3CBR%20%2F%3EDoug%20smith%3B%20Justin%20Smith%3CBR%20%2F%3EDoug%20smith%3B%20Tina%20smith%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3356499%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20Scripts%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356658%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356658%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319250%22%20target%3D%22_blank%22%3E%40islic%3C%2FA%3E%26nbsp%3BPerhaps%20PowerQuery%20is%20the%20tool%20for%20the%20job.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Riny_van_Eekelen_0-1652265063723.png%22%20style%3D%22width%3A%20434px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370726i7EB825799DC757BE%2Fimage-dimensions%2F434x149%3Fv%3Dv2%22%20width%3D%22434%22%20height%3D%22149%22%20role%3D%22button%22%20title%3D%22Riny_van_Eekelen_0-1652265063723.png%22%20alt%3D%22Riny_van_Eekelen_0-1652265063723.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EDemo%20attached%20using%20your%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3356848%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3356848%22%20slang%3D%22en-US%22%3EThank%20you%20for%20responding%2C%20that%20looks%20like%20a%20possible%20answer.%20I%20have%20tried%20a%20few%20attempts%20and%20haven't%20been%20able%20to%20duplicate.%20I%20keep%20running%20into%20%22unexpected%20errors%22.%20I%20don't%20really%20have%20any%20experience%20with%20PowerQuery%20but%20I%20will%20keep%20tinkering%20and%20maybe%20in%20the%20meantime%2C%20someone%20will%20have%20another%20idea.%20Thanks%20Riny!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3357215%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3357215%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319250%22%20target%3D%22_blank%22%3E%40islic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPower%20Query%20should%20definitively%20work%20(another%20option%20in%20the%20attached%20file).%20Alternative%20assuming%20you%20run%20Excel%202021%20or%20365%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22_Screenshot.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370772iC55FA1683C9190BB%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22_Screenshot.png%22%20alt%3D%22_Screenshot.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ein%26nbsp%3B%3CSTRONG%3ED3%3C%2FSTRONG%3E%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20%20%20replaced%2C%20%20SUBSTITUTE(B3%2C%22%3B%23%22%2C%22%3B%22)%2C%0A%20%20%20%20remove%2C%20%20%20%20TEXT(SEQUENCE(10%2C%2C0)%2C%220%22)%2C%0A%20%20%20%20split%2C%20%20%20%20%20MID(replaced%2CSEQUENCE(LEN(replaced))%2C1)%2C%0A%20%20%20%20filtered%2C%20%20FILTER(split%2CISNA(XMATCH(split%2Cremove)))%2C%0A%20%20%20%20concat%2C%20%20%20%20TEXTJOIN(%22%22%2C%2Cfiltered)%2C%0A%20%20%20%20cleaned%2C%20%20%20SUBSTITUTE(concat%2C%22%3B%3B%22%2C%22%3B%20%22)%2C%0A%20%20%20%20LEFT(cleaned%2CLEN(cleaned)-1)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3357375%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3357375%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F319250%22%20target%3D%22_blank%22%3E%40islic%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20can%20also%20be%20done%20in%20Excel%20365%20though%20there%20is%20some%20formula%20writing%20to%20be%20done.%26nbsp%3B%20The%20worksheet%20formula%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20SUBSTITUTE(%0A%20%20%20%20%20%20REDUCE(strings%2Cdigits%2CRemove%CE%BB)%2C%0A%20%20%20%20%20%20%22%3B%3B%22%2C%22%3B%20%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Euses%20REDUCE%20to%20call%20the%20Lambda%20function%20'Remove%CE%BB'%20for%20each%20character%20in%20'digits'%2C%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%22Remove%CE%BB%22%0A%3D%20LAMBDA(s%2Ct%2CSUBSTITUTE(s%2Ct%2C%22%22))%0A%0A%22digits%22%0A%3D%7B0%3B1%3B2%3B3%3B4%3B5%3B6%3B7%3B8%3B9%3B%22%23%22%7D%0A%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20537px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F370787iA26F26DB97AF45F2%2Fimage-dimensions%2F537x169%3Fv%3Dv2%22%20width%3D%22537%22%20height%3D%22169%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3357481%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3357481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F75890%22%20target%3D%22_blank%22%3E%40L%20z.%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlaying%20with%20your%20solution%2C%20I%20defined%20a%20Lambda%20function%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3ECleaned%CE%BB%20%3D%20LAMBDA(str%2C%0A%20%20%20%20LET(%0A%20%20%20%20%20%20%20%20replaced%2C%20SUBSTITUTE(str%2C%20%22%3B%23%22%2C%20%22%3B%22)%2C%0A%20%20%20%20%20%20%20%20remove%2C%20%20%20TEXT(SEQUENCE(10%2C%20%2C%200)%2C%20%220%22)%2C%0A%20%20%20%20%20%20%20%20split%2C%20%20%20%20MID(replaced%2C%20SEQUENCE(LEN(replaced))%2C%201)%2C%0A%20%20%20%20%20%20%20%20filtered%2C%20FILTER(split%2C%20ISNA(XMATCH(split%2C%20remove)))%2C%0A%20%20%20%20%20%20%20%20concat%2C%20%20%20TEXTJOIN(%22%22%2C%20%2C%20filtered)%2C%0A%20%20%20%20%20%20%20%20cleaned%2C%20%20SUBSTITUTE(concat%2C%20%22%3B%3B%22%2C%20%22%3B%20%22)%2C%0A%20%20%20%20%20%20%20%20LEFT(cleaned%2C%20LEN(cleaned)%20-%201)%0A%20%20%20%20)%0A)%3B%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eso%20the%20column%20of%20results%20is%20generated%20by%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3D%20MAP(strings%2C%20Cleaned%CE%BB)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3357762%22%20slang%3D%22en-US%22%3ERe%3A%20Cleaning%20up%20sharepoint%20export%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3357762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20definitively%20prefer%20your%20REDUCE%20solution.%20Thanks%20for%20sharing%20it%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

Riny_van_Eekelen_0-1652265063723.png

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

@islic 

 

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

 

_Screenshot.png

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 

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

image.png

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

@Peter Bartholomew 

I definitively prefer your REDUCE solution. Thanks for sharing it