How to extract names from email adress

Hello,

I want to extract the name of team member from an email adress, which I get as entry from the system in my sheet.

for example: [email protected] it should be writte in another cell: Joachim Armbruster

I tried following formula:

=UPPER(LEFT([Mitarbeiter_System]@row)) + (MID([Mitarbeiter_System]@row, 2, 1) + MID([Mitarbeiter_System]@row), 3, FIND("@", [Mitarbeiter_System]@row) - 3))


but it does not work

many thanks and regards

Joachim

Best Answer

  • =UPPER(LEFT([Mitarbeiter_System]@row,FIND(".",[Mitarbeiter_System]@row,1)-1) + " " + MID([Mitarbeiter_System]@row, FIND(".", [Mitarbeiter_System]@row, 1) + 1, FIND("@", [Mitarbeiter_System]@row) - FIND(".", [Mitarbeiter_System]@row) - 1)

    This should do what you want. It splits the string on "." and "@" giving you the information in between as a single string with a " " in between for readability.

Answers

  • =UPPER(LEFT([Mitarbeiter_System]@row,FIND(".",[Mitarbeiter_System]@row,1)-1) + " " + MID([Mitarbeiter_System]@row, FIND(".", [Mitarbeiter_System]@row, 1) + 1, FIND("@", [Mitarbeiter_System]@row) - FIND(".", [Mitarbeiter_System]@row) - 1)

    This should do what you want. It splits the string on "." and "@" giving you the information in between as a single string with a " " in between for readability.

  • Antonio Figueroa
    Antonio Figueroa ✭✭✭✭✭

    Try this. Change Column12 to your column and it should work.


    =LEFT([Column12]@row, FIND(".", [Column12]@row, 1) - 1) + " " + LEFT(RIGHT([Column12]@row, LEN([Column12]@row) - VALUE(FIND(".", [Column12]@row))), FIND("@", RIGHT([Column12]@row, LEN([Column12]@row) - VALUE(FIND(".", [Column12]@row)))) - 1)

  • Joachim Armbruster
    Joachim Armbruster ✭✭✭✭✭

    Many thanks for your quick and useful answers. Both solutions work.

    best regards

    Joachim

Help Article Resources