String Manipulation - Delete Data occuring multiple times in a cell

Blessy Clara

Board Regular
Joined
Mar 28, 2010
Messages
193
Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People, Elder abuse,Local Community, Elder abuse

<tbody>
</tbody>
Intellectual Disabilities, Finding work, Community Courses, Work

<tbody>
</tbody>
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

<tbody>
</tbody>
Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support, Holiday / Before / After School Programmes, Community Courses

<tbody>
</tbody>
Required
Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support, Community Courses

<tbody>
</tbody>


<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,406
Office Version
365
Platform
Windows
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.

Code:
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
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People, Elder abuse,Local Community, Elder abuse</td><td style=";">Youth Service - NEET / Youth Payment, Support and Training for Service Providers - Education, Programmes for Young People,  Elder abuse, Local Community</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Intellectual Disabilities, Finding work, Community Courses, Work</td><td style=";">Intellectual Disabilities, Finding work, Community Courses, Work</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">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><td style=";">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</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Other budgeting services, Local Community, Holiday / Before / After School Programmes,Parenting - Skills and Support, Holiday / Before / After School Programmes, Community Courses</td><td style=";">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><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B1</th><td style="text-align:left">=RemoveDupText(<font color="Blue">A1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
1,098,996
Messages
5,465,887
Members
406,453
Latest member
CeReaLK

This Week's Hot Topics

Top