Remove Values from Delimited String

[GonzoB]

Hi Helpers,

I have 2 hundred thousand records in a spreadsheet, one of the columns hold semicolon separated values. The number of values stored in this delimited string range from zero to a hundred.

What I would need to do is to remove a big number of values from this semicolon separated list, in a way that the format of the string does not change. The format is that the string 1) starts with ; 2) ends with ; 3) all distinct values are separeted by ;

The below simplified example illustrates how my records look like, what the format of the string is and how the values I need to remove look like.

Book15
	A	B	C	D	E	F
1	column A	column B				values to remove
2	Griezmann	;a;b;c;d;e;f;g;				a
3	Müller	;a;b;e;f;j;l;				d
4	Salas	;a;d;f;g;h;				g
Sheet1

Can you advise what would be the simplest and fastest way to removes the values from the semicolon separates strings?

Thanks in Advance

 

[JvdV]

Assuming at least a single value remains based on your rules. If not, then this would output a single semi-colon.

Book1
	A	B	C	D	E	F
1		Column B
2		;a;b;c;d;e;f;g;	;b;c;e;f;			a
3		;a;b;e;f;j;l;	;b;e;f;j;l;			d
4		;a;d;f;g;h;	;f;h;			g
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=MAP(B2:B4,LAMBDA(x,LET(y,TEXTSPLIT(x,,";"),TEXTJOIN(";",0,FILTER(y,COUNTIF(F2:F4,y)=0)))))
Dynamic array formulas.

 

[GonzoB]

Hi JvdV,

thank for the formula, it works perfectly for small data sets. However when I'm running it for 50 000 records, the results I get is "0" in all the rows. Do you know if there's a way to overcome this?

 

[GonzoB]

After loading for a while the zeros turned into an error: #SPILL!

I'm looking into the reason, but if you have any suggestions, please let me know.

Many thanks

 

[shinigamilight]

You can try This custom formula. the first argument is the cell you want to remove strings from, 2nd argument is the strings you want to remove.

Function Reg(rng As Range, Pat As Range)
        Dim Regex As Object
        Set Regex = CreateObject("vbscript.regexp")
        Dim Fin, Ra, s
        Fin = Pat
        Dim k As Long
        ReDim Ra(1 To Pat.Cells.Count)
       
        For Each s In Fin
            k = k + 1
            Ra(k) = s
        Next s
        Dim mr As String: mr = Join(Ra, "")
       
        With Regex
        .Global = True
        .Pattern = "[" & mr & "];"
        Reg = .Replace(rng.Value, "")
        End With
       
End Function

 

[JvdV]

After loading for a while the zeros turned into an error: #SPILL!

I'm looking into the reason, but if you have any suggestions, please let me know.

Many thanks

@GonzoB I've tested it myself on 50000 rows just now and worked fine. The spill error could indicate that you have data sitting in the way of the dynamic array. Make sure the column where you put this formula has enough empty rows for it to spill the results correctly.

 

[GonzoB]

Hi JvdV,

thanks for looking into it. I could not find the reason, but somehow at the end I managed to run the formula against 90% of my records.

Have a nice day.