Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Want to delete rows | Excel Discussion (Misc queries) | |||
Find last occurance of text in range | Excel Worksheet Functions | |||
Find within Workbook. | Excel Discussion (Misc queries) | |||
Excel - Find & Replace text in a string | Excel Worksheet Functions | |||
How do I find the cell address of the 2nd largest of a set? | Excel Discussion (Misc queries) |