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 use CONCATENATE, YEAR, MONTH, DAY and LEN formulas to create unique IDs

Prerequisite 

Know how to use "CONCATENATE", "YEAR", "MONTH" "LEN" and  "DAY" formulas individually.
Please refer to Microsoft help for Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.

Possible Usage

To create unique IDs using data code or time stamp. This is more applicable to operation or manufacturing environment.

example: create IDs using dates.

The steps (How to do)

1. type =YEAR(A2) in cell B2

2. type  =MONTH(A2) in cell C2

3. type =DAY(A2) in cell D2

4. Add running numbers in column E. Use "text" format to make sure zeros appear in front.

5. type =concatenate(B2,C2,D2,E2). Drag or copy paste the formula all the way (to cover the all the dates)

Note that the ids are not of the same length. 

To avoid you can use IF and LEN as described below.

2a. type =IF(LEN(MONTH(A2))=1,CONCATENATE("0",MONTH(A2)),MONTH(A2)) in column C 

3a. type =IF(LEN(DAY(A2))=1,CONCATENATE("0",DAY(A2)),DAY(A2)) in Column D

The result will be as follows

Here we go nice and clean IDs.

Done.

All comments are welcome.