Excel display filetype or all chars after last full stop

0

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. 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

Share.

About Author

Comments are closed.

Please note we do not accept any responsibility for errors occurring During Use of this site users follow instructions listed at their own risk.