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

Resources