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
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
========
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!
