FIND 2nd character in a string

  • Thread starter Thread starter Fuzzy
  • Start date Start date
F

Fuzzy

how do i use the FIND function to get the 2nd character?

for eg:

A1 has 005/0101/78445945/2002

the entire column has strings like the above....

i want only those characters after the 2nd "/" and before the 3rd "/"
what formula to use?

in the above result, the answer should be - 78445945
 
Hi Fuzzy,

The Substitute function allows you to specify an instance # so that you can
identify where the 2nd instance of "/" is:

SUBSTITUTE(text,old_text,new_text,instance_num)

Substitute("\",a1,"unique character of your choice",2)

Then you can use Find:

Find("unique character of your choice",(substitute("\",a1,"unique character
of your choice",2),1)

Hope this helps.

Peggy
 
Peggy - Thanks a ton!!!! Was simple and worked!!
Btw, the syntax for substitute was slightly wrong... :)
Correct - Substitute(a1,'/',"unique character of your choice",2)
 
Perfect!!!
It worked!! Thank you....

Was wondering how to get the second "/" in that mid formula....
Coz i had used the mid as well as the find formula... just dint know how to
get the 2nd "/"

God Bless!
 
Thanks a ton... it took me some time to figure out the cumbersome formula...
but finally got it....

and nope, dint try the user defined function..... :(
 
Back
Top