2003: find email addresses

QuestionEdit

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.

AnswerEdit

Other than the vague description of the table, not much I can tell you.

If you have2007 or later, the you will be able to transpose the table. If not, you won't because earlier versions ofonly have 256 columns and you have 300 rows that you want to transpose

also, it is unclear what you mean by pull out, whether you are using formulas or you are looking for a vba solution, why you think transposing the table is necessary, whether each row is unique in terms of the teachers name so you basically want to find a cell containing an @ symbol in each column and say reproduce that cell in column 15

if you want that, then assuming you have all the information on a teacher in a single row in columns A to O, then you can do

P2: =INDEX($A2:$O2,1,MATCH("*@*",$A2:$O2,0))

and drag fill down the column. You should have the email addresses in column P.

Let me know more information if that doesn't work for you.