String Manipulation - Delete Data occuring multiple times in a cell

[Blessy Clara]

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 374"]
<tbody>[TR]
[TD="class: xl63, width: 374"]Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People, Elder abuse,Local Community, Elder abuse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 374"]
<tbody>[TR]
[TD="class: xl65, width: 374"]Intellectual Disabilities, Finding work, Community Courses, Work[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 374"]
<tbody>[TR]
[TD="class: xl65, width: 374"]Sexual harm / sexual violence,Victim / survivor of family violence,Violent person, Sexual harm / sexual violence, Crime and Its Effects,Sexual harm / sexual violence, Counselling,Crime and Its Effects,Sexual harm / sexual violence,Women, Counselling - Youth,Helplines and Information Services - Youth, Counselling[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 374"]
<tbody>[TR]
[TD="class: xl65, width: 374"]Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support, Holiday / Before / After School Programmes, Community Courses[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD]Required
[TABLE="width: 500"]
<tbody>[TR]
[TD]Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support, Community Courses[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]

[/TD]
[/TR]
</tbody>[/TABLE]

I would like to remove a term occurring multiple times within the cell
Sometimes the word may occur as Health, Health - Mental, Health - Gynec, Health
Do not remove Health occurring as Health - Mental, Health Gyenec - Delete only Health

Clue - the words are separated by Comma
Thank you in advance

 

[FormR]

Hi, here is a UDF that you can try.

To use:
1. With your spreadsheet open, press ALT+F11 to open the VBE.
2. Click "Insert" > "Module" on the menu bar.
3. Copy and paste the code below into the blank window on the top right hand side.
4. Press ALT+Q to close the VBE
5. Save your workbook as a macro enabled workbook (xlsm)
6. Use just like any another worksheet function as demonstrated below.

Function RemoveDupText(s As String)
Dim v
For Each v In Split(s, ",")
  If InStr(1, ", " & RemoveDupText & ", ", ", " & Trim(v) & ", ", vbTextCompare) = 0 Then
    RemoveDupText = RemoveDupText & ", " & Trim(v)
  End If
Next v
RemoveDupText = Mid(RemoveDupText, 3)
End Function

Excel 2013/2016
	A	B
1	Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People,Elder abuse,Local Community,Elder abuse	Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People, Elder abuse, Local Community
2	Intellectual Disabilities, Finding work, Community Courses, Work	Intellectual Disabilities, Finding work, Community Courses, Work
3	Sexual harm / sexual violence,Victim / survivor of family violence,Violent person,Sexual harm / sexual violence, Crime and Its Effects,Sexual harm / sexual violence, Counselling,Crime and Its Effects,Sexual harm / sexual violence,Women, Counselling - Youth,Helplines and Information Services - Youth, Counselling	Sexual harm / sexual violence, Victim / survivor of family violence, Violent person, Sexual harm / sexual violence, Crime and Its Effects, Counselling, Women, Counselling - Youth, Helplines and Information Services - Youth
4	Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support,Holiday / Before / After School Programmes, Community Courses	Other budgeting services, Local Community, Holiday / Before / After School Programmes, Parenting - Skills and Support, Holiday / Before / After School Programmes, Community Courses
Sheet1

Cell Formulas
Range	Formula
B1	=RemoveDupText(A1)

 

[Blessy Clara]

Hi FormR

Thank you very much for the UDF, greatly helpful