Search code examples
excelif-statementsearchexcel-formulaexcel-2007

Separate a column into two columns

I am using Excel 2007. I have a column in my Excel spreadsheet. It contains data that should be separated into two columns. In this column, some rows are dates. Some are email addresses. Some are empty.

I want to import the data via SSIS into a SQL table. I think the best way is to create a date column and an email column. Where the row is empty or an email address, the date for that row will be null. Where the row is empty or a date, the email for that row will be null. The date column will need to be date format for the SSIS import. The email column needs to be text format.

How do I do this?

Solution

  • If your data starts in Row1 of ColumnA then:

    in B1: =IF(ISNUMBER(A1),A1,"") and
    in C1: =IFERROR(IF(SEARCH("@",A1)>0,A1,""),"")

    and copied down to suit should split the source into columns by type.