Archive of Mr Excel Message Board


Back to Data in Excel archive index
Back to archive home



Ability to split up a text field

Posted 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 "jonathan@gosure.com" 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.

Thx Jonathan


Re: Ability to split up a text field

Posted 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.

Aladin

========


Re: Ability to split up a text field

Posted 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...
menu command

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.