Search code examples
sqlexceltext-to-column

Create separate column based on email address, using Excel or SQL

I have a column in Excel with the following

Employee
Mickey D. Mouse [email protected]

I want it to look like

Employee || Email
Mickey D. Mouse || [email protected]

In Excel, if I use Text to Column it looks like this

Mickey || D. || Mouse || [email protected]

So how can I separate on JUST the email, and leave the name in ONE column?

Please provide help in either Excel or SQL.

Solution

  • OK I just got the email out with this from https://www.extendoffice.com/documents/excel/1272-excel-extract-email-address.html

    =IFERROR(TRIM(RIGHT(SUBSTITUTE(LEFT(AL2,FIND(" ",AL2&" ",FIND("@",AL2))-1)," ",REPT(" ",LEN(AL2))),LEN(AL2))),0)

    And extracted the name with

    =IF(J2=0, (LEFT(H2, SUM(LEN(H2)-LEN(J2)))), (LEFT(H2, SUM(LEN(H2)-LEN(J2)-1))))