Archive of Mr Excel Message Board
Back to Data in Excel archive index
Back to archive home
Ability to split up a text fieldPosted by Columbusuk on August 16, 2001 3:47 AM
I want to be able to sift through a long columm of e-mail address and recognise what comes after the "@" sign. ie Field is "firstname.lastname@example.org" I want to split off "gosure.com" into a separate field thus creating a new column.
While we are about it, I also want to identify those addresses which do not have an "@" sign or do not have <something>.<something> and flag these so they then in a further operation be transferred.
Re: Ability to split up a text fieldPosted by Aladin Akyurek on August 16, 2001 4:21 AM
Assuming that the first entry is in A2,
in B2 enter: =IF(ISNUMBER(SEARCH(".",A2)),IF(ISNUMBER(SEARCH("@",A2)),RIGHT(A2,LEN(A2)-SEARCH("@",A2)),"Error: @ is missing"),"Error: No dots")
Copy down this as far as needed.
Re: Ability to split up a text fieldPosted by Mark W. on August 16, 2001 8:59 AM
Jonathan, you can also use Excel's Text to Columns
facility. Assuming that your list resides in
column A and that column B is blank (if, not insert
a new column B)...
1. Select column A and choose the Data | Text to Columns...
2. At Step 1 of 3 choose "Delimited" and press [ Next> ]
3. At Step 2 of 3 check "Other",enter "@" into
it's corresponding field, and press [ Finish ].
Treat a blank cell in column B as your "flag" for
further processing of your problem addresses. In
fact you can filter your worksheet on column B
expressly looking for these blanks!
This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store
to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.