Delete All??

[Geek Girl 007]

From this table, can I delete, just 2 colours? i.e. all the Blue and Pink?

Name	Colour
Jane	Blue, Pink Green, Purple, Red, Orange
Peter	Pink, Green, Red
Carol	Orange, Pink
Eva	Blue, Purple, Orange
Jodie	Green, Purple, Red, Pink
David	Red, Purple
Edwin	Green, Blue

 

[Cubist]

Try:

Book3
	A	B	C
1	Name	Colour
2	Jane	Blue, Pink Green, Purple, Red, Orange	Pink Green, Purple, Red, Orange
3	Peter	Pink, Green, Red	Green, Red
4	Carol	Orange, Pink	Orange
5	Eva	Blue, Purple, Orange	Purple, Orange
6	Jodie	Green, Purple, Red, Pink	Green, Purple, Red
7	David	Red, Purple	Red, Purple
8	Edwin	Green, Blue	Green
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(s,SUBSTITUTE(SUBSTITUTE(B2&", ","Blue, ",""),"Pink, ",""),LEFT(s,LEN(s)-2))

 

[Micron]

I was going to suggest vba so that user could pass whatever the desired value is at any point. However, it would depend on consistent data and there is no comma between Pink Green in one cell.

 

[JEC]

If you want the table being modified without the extra column, run this in VBA

Sub jec()
Cells(1).CurrentRegion.Columns(2).Name = "ar"
[ar] = [let(s,substitute(substitute(ar&", ","Blue, ", ""),"Pink, ",""),left(s,len(s)-2))]
End Sub

 

[Geek Girl 007]

I was going to suggest vba so that user could pass whatever the desired value is at any point. However, it would depend on consistent data and there is no comma between Pink Green in one cell.

VAB is not very compatible with my work computer

 

[Geek Girl 007]

If you want the table being modified without the extra column, run this in VBA

Sub jec()
Cells(1).CurrentRegion.Columns(2).Name = "ar"
[ar] = [let(s,substitute(substitute(ar&", ","Blue, ", ""),"Pink, ",""),left(s,len(s)-2))]
End Sub

VAB is not very compatible with my work computer

 

[Geek Girl 007]

Try:

Book3
	A	B	C
1	Name	Colour
2	Jane	Blue, Pink Green, Purple, Red, Orange	Pink Green, Purple, Red, Orange
3	Peter	Pink, Green, Red	Green, Red
4	Carol	Orange, Pink	Orange
5	Eva	Blue, Purple, Orange	Purple, Orange
6	Jodie	Green, Purple, Red, Pink	Green, Purple, Red
7	David	Red, Purple	Red, Purple
8	Edwin	Green, Blue	Green
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(s,SUBSTITUTE(SUBSTITUTE(B2&", ","Blue, ",""),"Pink, ",""),LEFT(s,LEN(s)-2))

Hi There,
Yes this worked perfectly, however wen I tried it on a larger scale I was unable to change and add to it?

The report i run is automatically generated (poorly) and I may need to add and change what I want to omit in the future.
This is the report:

Parameters

Code:

C1

Code

Surname

First Name

Loc

Age

Eth. Code

Nat. Code

Rel. Code

Marital Status

Mat. Status

Disab?

Language

Activity Description AM and PM

K

52

M1

BRIT

NIL

S

N/A

N

ENG

BICS

G

38

W9

ROMA

EORTH

C

N/A

N

CLASS-ESOL 1

H

47

W9

ROMA

CHRST

M

N/A

N

CLASS-ESOL 1, CE Service, CEFC Newsletter

K

58

W9

POLE

BUDD

M

N/A

N

Biohazard Cleaner

G

37

A1

INDI

MOS

P

N/A

N

CLASS-KITCHEN

C

20

W1

BRIT

COFE

S

N/A

N

C Painter, CE Service, CEFC Newsletter

D

50

W9

POLE

RC

S

N/A

N

D Painter

B

28

O9

BRIT

CHRST

N

N/A

N

CE Service

H

49

A9

VIET

BUDD

M

N/A

N

CLASS-ESOL 1

D

40

B9

BRIT

OTH

Not Entered

N/A

Y

D Painter

D

49

B9

BRIT

COFE

S

N/A

N

CE Service, CEFC Newsletter, BICS

H

33

B2

NIGERIA

BAPT

M

N/A

N

CE Service, CEFC Newsletter, Biohazard Cleaner

C

32

W1

BRIT

NIL

S

N/A

N

CLASS-Catering

H

54

W9

HUNG

RC

M

N/A

N

CLASS-ESOL 1

H

49

W9

POLE

RC

C

N/A

N

CLASS-KITCHEN

D

55

W1

BRIT

NIL

S

N/A

N

D Painter

In S6 I added: =LET(s,SUBSTITUTE(SUBSTITUTE(R6&", ","CE Service, ",""),"D Painter, ",""),"CEFC Newsletter, ",""),LEFT(s,LEN(s)-2))
and I got this:

I was trying to be clever, but I'm not sure what I did
Can you help?

 

[FormR]

Hi, here's another way you could try:

Book2
	A	B	C
1	Name	Colour
2	Jane	Blue, Pink Green, Purple, Red, Orange	Pink Green, Purple, Red, Orange
3	Peter	Pink, Green, Red	Green, Red
4	Carol	Orange, Pink	Orange
5	Eva	Blue, Purple, Orange	Purple, Orange
6	Jodie	Green, Purple, Red, Pink	Green, Purple, Red
7	David	Red, Purple	Red, Purple
8	Edwin	Green, Blue	Green
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(TS,TEXTSPLIT(B2,", "),TEXTJOIN(", ",1,FILTER(TS,NOT(ISNUMBER(MATCH(TS,{"Pink","Blue"},0))),"")))

 

[Geek Girl 007]

Hi, here's another way you could try:

Book2
	A	B	C
1	Name	Colour
2	Jane	Blue, Pink Green, Purple, Red, Orange	Pink Green, Purple, Red, Orange
3	Peter	Pink, Green, Red	Green, Red
4	Carol	Orange, Pink	Orange
5	Eva	Blue, Purple, Orange	Purple, Orange
6	Jodie	Green, Purple, Red, Pink	Green, Purple, Red
7	David	Red, Purple	Red, Purple
8	Edwin	Green, Blue	Green
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=LET(TS,TEXTSPLIT(B2,", "),TEXTJOIN(", ",1,FILTER(TS,NOT(ISNUMBER(MATCH(TS,{"Pink","Blue"},0))),"")))

PERFECT and adaptable. Thank you

 

[Cubist]

PERFECT and adaptable. Thank you

TEXTSPLIT isn't available in 2021 as shown in your profile.