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 mickey@disney.com

I want it to look like

Employee || Email
Mickey D. Mouse || mickey@disney.com

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

Mickey || D. || Mouse || mickey@disney.com

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