Remove values from a Cell based on a list

Hello All,

For a school project i am trying to achieve the following in excel.

I have an input which contains many values with a ";" in between.
This input has too many values, certain values must be deleted. Therefore i created a REMOVE-list of more than 4000 values.
The output must be only numbers from the input that are not in the REMOVE-list.

See image below as an example.

xe.png


What is the best method of doing this?

EDIT: i tried the SUBSTITUTE function, but it doesn't work with 4000 values.

Thank in advance, and merry christmas to all!

Is there just one input cell, or a column of them

If one then I'd be inclined to split the input cell across many columns with the Data Text To Columns functionality.
Then use a VLOOKUP in the row underneath to check that the individual values exist in your values to be removed.
in the row underenath that use an IF function to return the value if it's not in the list and concatenate it with the value in the cell to the left. Copy that across and your final cell will contain the values you seek.

Then copy that final cell and paste it as values.

You could use that same method even if you have many rows, you'd just need to add some blank rows between each cell so that you have some room for the working columns.


Otherwise a VBA solution.

With input at A2 and Remove list at C2:C9999

B2 and drag down

Formula:

Please Login or Register  to view this content.

or with Excel365 textjoin, press CTRL+Shift+Enter

Formula:

Please Login or Register  to view this content.

Or try this:
1. Enter in B2 and copy down

Formula:

Please Login or Register  to view this content.

2. Enter in C2 and copy down

Formula:

Please Login or Register  to view this content.

3. Enter in E2

Formula:

Please Login or Register  to view this content.

v	A	B	C	D	E
1
2	250;380;485;15;84;895;4852;518;485	250	#N/A	15	250;895
3		380	20	16
4		485	16	17
5		15	1	18
6		84	11	19
7		895	#N/A	20
8		4852	19	50
9		518	12	81
10		485	16	82
11				83
12				84
13				518
14				519
15				520
16				484
17				485
18				4850
19				4851
20				4852
21				380
22				381