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 |
find nth position of a string
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" wrote in message ... 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 |
find nth position of a string
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 |
find nth position of a string
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 |
find nth position of a string
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 |
find nth position of a string
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 |
find nth position of a string
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 |
All times are GMT +1. The time now is 03:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com