Removig Duplicates
 

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

Removig Duplicates
 

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


"dk" wrote in message
...
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