sorry for the late reply:
you can refer to this tutorial i did here to find the last occurrence of a certain character. it's at trick number 14.
http://www.excelforum.com/tips-and-t...e-learned.html
so for the first formula i provided you:
=MID($A4,FIND("^",SUBSTITUTE($A4,"-","^",LEN($A4)-LEN(SUBSTITUTE($A4,"-",""))))-20,19)
the red portion helps me find where's the last "-". the Date & Time stamp is a standard length of 19 characters. but to go from the last "-" to the Date/Time Stamp also includes a space, so it's actually -19 -1 (-20). the 19 characters then can be used for the MID formula's num_chars portion as well.
the 2nd & 3rd formula are similar, but just looking for different characters. this time, when i find the last "-", i want to start 2 characters later, where the name is.
to know the length of the name, i then i need to find when is the last occurrence of "Approval history" minus the last "-" position & minus 4 characters (basically 1 to go back to the character before "Approval history", 2 for the spaces after "-" & after the name & 1 more for the open bracket.
1 slight difference here is the last argument of the SUBSTITUTE formula where the instance_num is. notice my first formula is:
LEN($A4)-LEN(SUBSTITUTE($A4,"-",""))
if you read my tutorial, it's to find how many times the paricular character appeared. i basically find how long is the full text in A4 minus the length of it when i substitute "-" with blanks. so it's 437 - 428. hence, there are 9 "dashes".
but when i'm looking for characters like "Approval history", where there is more than 1 character, then it makes no sense. the length when i substitute the characters would be skewed. say for eg i have this text:
apples are apples.
if you do this formula:
=LEN($A4)-LEN(SUBSTITUTE($A4,"apples",""))
it will give you 12. but "Apples" only appeared twice. so we need to do:
=(LEN(A5)-LEN(SUBSTITUTE($A5,"apples","")))/LEN("apples")
6 is the length of the text "apples". so similarly, what i did in the formula is:
(LEN($A4)-LEN(SUBSTITUTE($A4,"Approval history","")))/LEN("Approval history")
the 3rd formula is just using "approval for" instead of "-". and "approved by" instead of "Approval history". hope that helps
Bookmarks