Deleting repeating text from a column where each cell in the column has multiple text phrases

[pbhogal]

deleting repeating text from a column where each cell in the column has multiple text phrases​

Hi

I have an excel file with a column that has over 500 cells. Each cell has multiple text phrases in it seperated by a comma or a semi-colon. What I would like to do is delete all the recurring phrases in the column as a whole so that I am left with text in a new column that has only the unique text phrases in it as identified from the original 500 cells.

Can someone help in this regard?

THanks.

 

[Sahak]

Hi,

To make it more clear, could you please attach screenshots with examples showing data before & data after?

 

[Peter_SSs]

This might also help:
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

However, assuming data in column A, starting in row 2 & results in column B, you could try this with a copy of your data.

Sub Phrases1()
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, k As Long
  
  a = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To Rows.Count, 1 To 1)
  For i = 1 To UBound(a)
    For Each itm In Split(Replace(a(i, 1), ",", ";"), ";")
      k = k + 1
      b(k, 1) = Trim(itm)
    Next itm
  Next i
  With Range("B2").Resize(k)
    .Value = b
    .RemoveDuplicates 1, xlNo
  End With
End Sub