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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,499
Office Version
  1. 365
Platform
  1. 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,129,931
Messages
5,639,064
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top