Questions

I use MS Excel 2010. I have a list that conains email and non-email address

Tags:
+
0 Votes
Locked

I use MS Excel 2010. I have a list that conains email and non-email address

edwardfowler
I used the FIND and MID commands,using the "@" as the midstring but could only sort the fields to the right, truncating the name on the left. Is there a way to give me both halves of the string (full email address) or another way to do it?
Thanks

Edward
  • +
    0 Votes
    robo_dev

    I am not an Excel guru, but I would parse/split the data first, then sort on the two halves.

    The term for that is creating a 'helper column'

    +
    0 Votes
    edwardfowler

    Thanks, but this trucates the field, eliminating everything to the right of the "@"

    +
    0 Votes
    johbelnel

    If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
    This should give you the part in front of the "@"
    then type this in C1
    =IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
    This should give you the part between the "@" and the first "."

    Hope this helps.

    +
    0 Votes
    johbelnel

    If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
    This should give you the part in front of the "@"
    then type this in C1
    =IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
    This should give you the part between the "@" and the first "."

    Hope this helps.

  • +
    0 Votes
    robo_dev

    I am not an Excel guru, but I would parse/split the data first, then sort on the two halves.

    The term for that is creating a 'helper column'

    +
    0 Votes
    edwardfowler

    Thanks, but this trucates the field, eliminating everything to the right of the "@"

    +
    0 Votes
    johbelnel

    If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
    This should give you the part in front of the "@"
    then type this in C1
    =IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
    This should give you the part between the "@" and the first "."

    Hope this helps.

    +
    0 Votes
    johbelnel

    If your 1st e-mail address is in A1, type the following in B1: =IF(ISERROR(FIND("@",A1))=TRUE,"",LEFT(A1,FIND("@",A1)-1))
    This should give you the part in front of the "@"
    then type this in C1
    =IF(ISERROR(FIND("@",A1))=TRUE,"",MID(A1,FIND("@",A1)+1,FIND(".",A1)-FIND("@",A1)-1))
    This should give you the part between the "@" and the first "."

    Hope this helps.