FREE SOFTWARE  Click here to Download the Free HPC Solution Toolbar NOW  FREE SOFTWARE  


Excel display filetype or all chars after last full stop Useful formula to display all text after the last full stop =RIGHT(A1, LEN(A1)(FIND("#",SUBSTITUTE(A1,".","#",LEN(A1)LEN(SUBSTITUTE(A1,".","")))))) How does this work? Let's look at an example: We want to find the right hand side of 192.168.0.254 after the last full stop e.g 254 1. The last part LEN(A1)LEN(SUBSTITUTE(A1,".","") removes all the .'s and give a length of this string 1921680254 value is 10 2. This is subtracted from the length of A1 e.g. 1310 = 3 This tells us how many .'s in the string 3. The next part FIND("#",SUBSTITUTE(A1,".","#",3) substitutes the 3rd . for a # 4. Now it just works out where the # is placed, subtracts that from the length to give number of characters to take from the right 3 5. Finally, 3 characters are taken from the right of the string, e.g. 254 Change the formula as required, so you could find the last @ sign by replacing the .'s with a @'s sign Previous Page Next Page IP  
GOOGLE HELPOUTS  