a collection of "forulars" i have used in Excel
Capture the last segment[edit]
The first one is what I found on [Mr Excel]
The second is what I wanted =)
Very nice logic I thourght.
=REPLACE(A1,1,SEARCH("@",SUBSTITUTE(A1," ","@",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),"") - extracts the last 2 space seperated segments - ie "moo foo" from "who are moo foo"
=REPLACE(H12,1,SEARCH("@",SUBSTITUTE(H12,"-","@",LEN(H12)-LEN(SUBSTITUTE(H12,"-","")))),"") - extracts the last minus seperated segment - ie "foo" from "who-are-moo-foo"
Quick dirt explanation (using above example)
[1]
LEN(H12)-LEN(SUBSTITUTE(H12,"-",""))
"Length of the target cell" - "Length of the target cell with out the seperator"
Len "who-are-moo-foo" = 15 - len "whoaremoofoo" = 12
= 3
[2]
SUBSTITUTE(H12,"-","@",[1])
Sub the [1]th "@" for a "-" in target cell.
Substitute the 3rd - in "who-are-moo-foo" with @
= "who-are-moo@foo"
[3]
REPLACE(H12,1,SEARCH("@",[2]),"")
Replace from char 1 to the "@" for string "who-are-moo@foo"
= "foo"