Excel display filetype or all chars after last full stop
 Windows Fix | About Us | Which Computer? | Memory Stick | Copy File | Site Map | 
What is a Browser
 | Search Engine | Windows Explorer 

Search for :

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 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. 13-10 = 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


How to fix anything