MrExcel Consulting
Your One Stop for Excel Tips & Solutions

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 "" I want to split off "" 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.



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!