find nth position of a string

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

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





"TUNGANA KURMA RAJU" <TUNGANAKURMARAJU@discussions.microsoft.com> wrote in
message news:59C3F921-B3E7-462F-92A7-3FAF88FE6215@microsoft.com...
> 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

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


TUNGANA KURMA RAJU wrote:
> 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

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" wrote:

> 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
>
>
> TUNGANA KURMA RAJU wrote:
> > 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
>

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


TUNGANA KURMA RAJU wrote:
> 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" wrote:
>
>
>>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
>>
>>
>>TUNGANA KURMA RAJU wrote:
>>
>>>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
>>

a million thanks Mr.Roger

"Roger Govier" wrote:

> 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
>
>
> TUNGANA KURMA RAJU wrote:
> > 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" wrote:
> >
> >
> >>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
> >>
> >>
> >>TUNGANA KURMA RAJU wrote:
> >>
> >>>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
> >>
>

Hi

You're very welcome. Thanks for the feedback.

Regards

Roger Govier


TUNGANA KURMA RAJU wrote:
> a million thanks Mr.Roger
>
> "Roger Govier" wrote:
>
>
>>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
>>
>>
>>TUNGANA KURMA RAJU wrote:
>>
>>>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" wrote:
>>>
>>>
>>>
>>>>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
>>>>
>>>>
>>>>TUNGANA KURMA RAJU wrote:
>>>>
>>>>
>>>>>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
>>>>