moving email addresses between columns

Microsoft Excel 2003 (full product)
October 25, 2009 at 15:24:59
Specs: Windows XP

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

row2/column1: Name2

and I need them like this:

r1/c1: Name1 r2c2 Email1
r2c1: Name2 r2c2 Email2

Some examples of the raw data:

Smith, John
Doe, J Jack
Doe, Doe John

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.

See More: moving email addresses between columns

Report •

October 25, 2009 at 16:14:21
Try using Text to Columns:

Highlight your Cell
Then on the Task Bar
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.


Report •

October 25, 2009 at 17:22:01
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.


Report •

October 25, 2009 at 17:33:40
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))


Report •

Related Solutions

October 26, 2009 at 03:34:20
thanks, it really helped :)

Report •

October 26, 2009 at 03:42:13
one more thing 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.


Report •

October 26, 2009 at 07:11:04

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.


Report •

October 26, 2009 at 07:18:45
Excel was not designed to manipulate text, so there is no cut function.

You can get the Left most string with

=LEFT(A1,FIND(" ",A1,1))

You can get the Right most string with

=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

But I am unable to get the middle string, between the first space character and the last space character because there is a middle space sometimes and sometimes not.

You will need some type of VBA to get everything you want.

You can get very close using the Text to Columns function, cutting up the entire cell using the space character as you delimiter. You need then only move the email address's that don't line up.


Report •

October 26, 2009 at 07:39:48
If you want all but the last text string:

1 - Determine the length email address, which appears to be the last text string in your examples. Use Mike's formula to grab the email address and use LEN to find it's length. Add 1 to it to account for the space before the email address:

=LEN(RIGHT(A37,LEN(A37)-FIND("#",SUBSTITUTE(A37," ","#",LEN(A37)-LEN(SUBSTITUTE(A37," ",""))))))+1

2 - Subtract this number from the length of the entire string to determine the number of characters in the text proceeding the space before the email address:

=LEN(A37) - (LEN(RIGHT(A37,LEN(A37)-FIND("#",SUBSTITUTE(A37," ","#",LEN(A37)-LEN(SUBSTITUTE(A37," ",""))))))+1)

3 - Use this value as the num_chars argument for a LEFT function:

=LEFT(A37,LEN(A37) - (LEN(RIGHT(A37,LEN(A37)-FIND("#",SUBSTITUTE(A37," ","#",LEN(A37)-LEN(SUBSTITUTE(A37," ",""))))))+1))

Report •

October 26, 2009 at 08:35:02

I get a headache just looking at that formula.....
But it works and it's a keeper.

Nice work.


Report •

October 26, 2009 at 08:47:01
You did the hard part...I just figured out how to use the length of the string to get the results the OP wanted.

But thanks!

Report •

October 27, 2009 at 07:30:03
works like a charm :)

thank you so much!

Report •

October 27, 2009 at 07:55:18
Just a posting tip:

When you receive multiple suggestions like you have in this thread, it helps if you tell us which solution (or solutions) "works like a charm."

We're not looking for bragging rights, but it might help someone else reading the thread know which method they might want to try first.


Report •

October 27, 2009 at 11:30:03
yeah, you're right. sorry about that.

I used the formula that you provided :)

Brag away :)(well deserved right)

Report •

October 27, 2009 at 12:56:10
See Responses 9 & 10.

We're all just here to help.

Report •

Ask Question