i need a help on the excel formulas
 

Dear Sir

how can i determine the 2nd occurrence for a string,

for example:

if i have the following string

D:\usama backup\Yanbu Project\HMI\Work done\usama.xls

and i need to detect the 2nd "\" or the third "\" or the last "\"

i can use find but find will allow me to determine the first "\" or
the "\" after a certain start number, but in case that i need the last
occurrence of the "\" i will not be able to use find

can you please help me on that

i need a help on the excel formulas
 

Try something like this to extract the substring starting from the nth
instance of "\" :

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"\",CHAR(1),n)) +1,255)

Where n = instance number

If n count of "\" then you'll get an error.

Count of "\":

=LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))

--
Biff
Microsoft Excel MVP


wrote in message
oups.com...
Dear Sir

how can i determine the 2nd occurrence for a string,

for example:

if i have the following string

D:\usama backup\Yanbu Project\HMI\Work done\usama.xls

and i need to detect the 2nd "\" or the third "\" or the last "\"

i can use find but find will allow me to determine the first "\" or
the "\" after a certain start number, but in case that i need the last
occurrence of the "\" i will not be able to use find

can you please help me on that