| Home |
| Search |
| Today's Posts |
|
#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 |
| 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) | |||
Threaded Mode
