find nth position of a string

[Guest]

Thanks in advance if you can plese give me a function to find
2/g/25/21/k/2/5
22/h/25/21/l/3/5
1. 4 th or 5th position string"/" in the above text strings.
2. After nth position what text is there?
3.4 th or 5th position of string"/" from right side ?
I have tried with right,left mid functions but didn't get correctt result

 

[Mangesh Yadav]

considering that you have the above in A1 and A2, then use:

1.
=MID(A1,4,1)
=MID(A2,5,1)

2. After nth position, (excluding position n)
=MID(A1,n+1,255)
(replace n with whatever number
If you want to include nth charater as well
=MID(A1,n,255)

3.
=MID(A1,LEN(A1)-(n-1),1)
replace n to 4 or 5
=MID(A1,LEN(A1)-(4-1),1) for the first one
=MID(A1,LEN(A2)-(5-1),1) for the second one


Mangesh

 

[Roger Govier]

Hi

Try
=FIND("^",SUBSTITUTE(A1,"/","^",4))
to find the position of the 4th occurrence of "/" in A1. Change 4 to 5 to
find the 5th etc.

To find the same thing starting from the right, you need to know how many
"/" there are, take 4 or 5 away, and find that occurrence from the left as
above.
To find the number of "/" in the cell use
=LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))

Regards

Roger Govier

 

[Guest]

Mr.Roger,Thanks a lot .My problem is solved 50%.I want to use the function
retrive text data that falls between nth position and nth position of
string"/".How?.Suppose my data in a1,a2 is
25/s/1/22/k/235
125/1/k/2/222
Extract text between 3 and 4th "/" in both the cases(excluding "/").
Thanks once again

 

[Roger Govier]

Hi

Then combine the formulae for the 3rd and 4th occurrences into a MID() formula

=MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",3))+1,FIND("^",SUBSTITUTE(A1,"/","^",4))-FIND("^",SUBSTITUTE(A1,"/","^",3))-1)
will return 22 as a text value
or to return the value as numeric, precede it with a double unary minus
=--MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",3))+1,FIND("^",SUBSTITUTE(A1,"/","^",4))-FIND("^",SUBSTITUTE(A1,"/","^",3))-1)

Regards

Roger Govier

 

[Guest]

a million thanks Mr.Roger

Hi

Then combine the formulae for the 3rd and 4th occurrences into a MID() formula

=MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",3))+1,FIND("^",SUBSTITUTE(A1,"/","^",4))-FIND("^",SUBSTITUTE(A1,"/","^",3))-1)
will return 22 as a text value
or to return the value as numeric, precede it with a double unary minus
=--MID(A1,FIND("^",SUBSTITUTE(A1,"/","^",3))+1,FIND("^",SUBSTITUTE(A1,"/","^",4))-FIND("^",SUBSTITUTE(A1,"/","^",3))-1)

Regards

Roger Govier

 

[Roger Govier]

Hi

You're very welcome. Thanks for the feedback.

Regards

Roger Govier