How to find the position of last of the multiple characters in a cell

Let's look at the example here. There are cells that contain something similar to 

drive1\folder1\file1023
drive2\folder2\subfolder2\file256
drive10\file34
..
..
..


The text after the last "/" is to be extracted, the result should be,

file1023
file256
file34
..
..

To do that you need position of the last "/". FIND function alone is not use because of two unknowns, one how many "/" s are there and second what is the "start number" to use.

But it is possible using RIGHT, FIND, SUBSTITUTE and LEN.
Let's say drive1\folder1\file1023 is in cell A1.

The formula to get file1023 will be

RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) Note '#' can be any character. Let's analyse the formula to see how it works. The formula works using [instance_num]  (Which is not available in FIND function)  of the SUBSTITUTE function and substituting "\" with "" (or nothing).   SUBSTITUTE(text, old_text, new_text, [instance_num]) Lets look at the formula from right to left. LEN(SUBSTITUTE(A1,"\","")) will give number of characters after replacing "\" with "". LEN(A1) will give number of characters before any change. The difference is the number of "\" in the cell, in this example 2. SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) will replace only the last "\" with "#" using 2 as the  [instance_num ]. Now "#" is in the last "\" position. Now using FIND the position of "#" can be found, in this example 15. Using RIGHT formula with identified position of the "#" it is possible to extract file 1023. LEN(SUBSTITUTE(A1,"\","")) 21 LEN(A1) 23 LEN(A1)-LEN(SUBSTITUTE(A1,"\","")) 2 SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))) drive1\folder1#file1023 FIND("#",SUBSTITUTE(A1,"\","#",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))) 15 RIGHT(A1,LEN(A1)-FIND("#",SUBSTITUTE(A1,"/","#",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))) file1023 Please let me know if you have any concerns or questions.

How to find duplicates in column without deleting them

It is a common requirement to find duplicates, in most cases the duplicates need to be deleted, but in some cases the duplicates need to be kept.

Excel 2010 (even 2007?) got the feature under "DATA" to remove duplicate entries in column(s), called Remove Duplicates. 

 

The issue with this tool it removes the duplicates and do not give more details.

If you want to mark the duplicates or want to VLOOKUP unique values you need use COUNTIF.

Let's see the example below, 

There are many duplicate entries i  column D. If you want to put "Duplicate" in column C, use

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

The result will be,

Important to note, if there is two or more values, all considered "DUPLICATE". This will not help if you are looking to use unique values. For example if you try to index the unique values, let's say in column B.

You may want to use,

=IF(C2="DUPLICATE","",MAX(B$1:B1)+1)

The result will be a "1" in B15. In other words there is only one unique value in column D. But we know it is not true. To solve this we need to change the COUNTIF slightly.

Instead of 

=IF(COUNTIF($D$2:$D$45,D2)>1,"DUPLICATE","")

We will use, 

=IF(COUNTIF(D$2:D2,D2)>1,"DUPLICATE","")

and the result is,

Now you can use VLOOKUP to pull the unique values, Vlookup(1,B:D,2,FALSE) and so on.

The Key Point is to leave the first instance of the duplicates as "Unique".



There will be lot of other ways to do the same. 
I will be happy to have any feedback.