Find email addresses

QuestionEdit

QUESTION: I have a file with about 300 rows and 14 columns. One of the columns has a teacher name. For each teacher, I need to pull out cells that contain an email address, in other words text that contains an @ (at) symbol.

Here's the progress I've made so far: I think maybe I need to first transpose the table (I'm having trouble with that because of the size of the table), and then set up a custom filter. Any help gratefully accepted.

ANSWER: I¡¯m having slight problems visualizing this, but I don¡¯t think you need to transpose. I¡¯m not clear if we have a random column with the @ sign or if it is always the same column ¨C IF it¡¯s the same column, then =SEARCH("@",H20) ¨C where H20 is the cell you are searching ¨C would do the job ¨C this would highlight the cells with an @ in them (by returning a number) and those without by returning an error. If it is a random column, then possibly the easiest way is to search all 14 columns (using 14 more columns) but amend the formula

=if(iserror(SEARCH("@",H20)),0,1)

Which would return a zero for no match and a 1 for a match ¨C you could then use that to get the data you want ¨C though again I¡¯m not clear what you want to do when you say ¡°pull out¡± - I am of course happy to help further in any way that helps - if it helps, my email is aidan.heritage@virgin.net

---------- FOLLOW-UP ----------

QUESTION: This is a not-so-smart question -- since I need to automate the copying, what do I do now? In other words, when I said 'pull out', I meant copy or extract. The basic idea is that the email addresses are scattered all over the place, and I would like to copy all the email addresses. For example, if row 1 has two cells containing an email address, I would like them to be put into cells J1 and K1.

AnswerEdit

Is putting them in consecutive cells actually helping in the ultimate goal (which of course is something I'm not aware of ) - for instance, although they would be more obvious, a mail merge would be a problem. PLUS and rather more importantly, it is possible to find the email by using the @ sign, but is there any consistency to the formatting - in other words, do we have a space both before and after the email address? It would PROBABLY be possible to construct a macro, but I'd ideally need to see some sample data.