Hi, Can someone please tell me, or provide a macro that can move email addresses from one column to another? I have a lot of names and emails in this format: row1/column1: Name1 Email1 row2/column1: Name2 Email2 and I need them like this: r1/c1: Name1 r2c2 Email1 r2c1: Name2 r2c2 Email2 Some examples of the raw data: Smith, John john.smith@dot.com Doe, J Jack dsa@dad.com Doe, Doe John d.s@sd.com Names are hyperlinks, emails aren't - just text(but I have a macro that can remove all hyperlinks if needed), and the separator between names and emails is a space (or maybe an Enter, not completely sure). The design above is just an example, it doesn't need to be like that, I just need the names on one column, and emails on another. Can someone please help me? Thanks a lot.

Try using Text to Columns: Highlight your Cell Then on the Task Bar Data Text To Columns When the Wizard comes up select Delimited Press Next Now check the box next to Space and see if that's gets you what you need. If yes, then simply click finish. MIKE http://www.skeptic.com/

If the Text to Columns does work, try this in cell A2: =RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ",""))))) Will copy all the info from the last space to the end of the cell. MIKE http://www.skeptic.com/

This should pull out the first text string IE Smith or Doe =LEFT(A1,FIND(" ",A1,1)) This should get you the middle text =LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1)) MIKE http://www.skeptic.com/

thanks, it really helped :)

one more thing though...how could I copy in another column all but the last text string(or just delete the last string)? copying the first, then the second string is not working too well for me. thanks!

Hi, You could also look at these three free text handling user defined functions. They allow text to be split using any delimiter, including space, or '@', and the delimiters can be counted from the end of the text as well as from the beginning. =xtndLeft(A1,",",1) will return all text up to the first comma. =xtndLeft(A1," ",-1) will return all the text up to the last space =xtndRight(A1," ",-1) will return all text from the last space to the end. =xtndMid(A1,",",1," ",1) would return all text between the first comma and the first space (if there was a space before the first comma, you could use =xtndMid(A1,",",1," ",2) and get the text from the first comma to the second space. Delimiters can also be longer pieces of text, so if you extract one word, you can use a reference to it as a delimiter in any of the functions. These are UDF's so the code has to be inserted into a VBA module. Regards