Removig Duplicates

  • Thread starter Thread starter dk
  • Start date Start date
D

dk

Is there anyway to remove duplicates when the first 3 words in cell are the
same even though the last few words are not the same
ex:
O:\01-English\01-ETnc\01-R7046-1\02-PrevLessons\04-chas
O:\01-English\01-ETnc\01-R7046-1\02-PrevLessons\09-Matot
O:\01-English\01-ETnc\01-R7046-1\02-PrevLessons\05-Dvar
O:\01-English\01-ETnc\01-R7046-1\02-PrevLessons\05-arimftim
O:\01-English\01-ETnc\01-R7046-1\02-PrevLessons\05-Dvaze
 
Try entering this formula into cell B1: assumes that your first string is in cell A1. This is an
array formula, so enter it using Ctrl-Shift-Enter

=SUBSTITUTE(A1,RIGHT(A1,LEN(A1)-MAX((MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1)="\")*ROW(INDIRECT("1:"
& LEN(A1))))),"")

Watch the line wrapping.

Then copy it down, and use those results to look for duplicates.

HTH,
Bernie
MS Excel MVP
 
Back
Top