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: joachim.armbruster@xxxxxxx.com 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

  • Johnathan Vargas Cruz
    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

  • Johnathan Vargas Cruz
    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.

  • 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)

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

    best regards

    Joachim

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!