Extract part of email cell text

Question

How can I extract part of the email after the last "."?

On the first line, I get what I want. On the second line the problem is that it is extracting after the first point and not the last.

With the following code:

=DIREITA(C3;NÚM.CARAT(C3)-PROCURAR(".";C3))

Answer 1

The problem is that the formula LOOK FOR returns the first occurrence of the character, while what you need is the index of the last occurrence.

To get the search behavior from right to left you need to implement this function yourself.

To create the function:

1. Press Alt+F11 in the Excel spreadsheet. The VBA window will open.
2. Click on the Vbaproject project name on the left
3. Choose Insert > Module.
4. Paste the code below into the Code Edit window that will open.

Public Function PROCURAR_DIREITA(ByVal text As String, ByRef cell As Range) As String

PROCURAR_DIREITA = InStrRev(cell.text, text)

End Function

5. Go back to your spreadsheet and replace the formula where it is:

=DIREITA(C3;NÚM.CARACT(C3)-PROCURAR(".";C3))

for:

=DIREITA(C3;NÚM.CARACT(C3)-PROCURAR_DIREITA(".";C3))