Excel - getting email addresses from cells

Zazen999

Or Fuzzy Ducket

isofa

Personally I'd write a VBA loop, the using the right and mid functions to go back from the end of the e-mail address until a space was found, then get that position, (you need the length of the string too) and then all the characters right of that output into a new cell, it's complex but would work first time!

You could probably do it with a complex formula too...

Or copy the entire list into a text editor (TextPad much better than notepad).

Search for the spaces and replace with ", " (that's comma space)

Then save the document as a text file.

Open (import) into excel as a CSV delimited file, put the data into separate cells, you should then have firstname, surname, email in 3 columns...

Zazen999

In an hour and a half I could have copied and pasted the email addresses to a new column...and had a coffee and some food.

timbim_2

There's the built in text to columns wizard. Can't see why you'd use anything else...

_Andy_

Zazen999 wrote: »

In an hour and a half I could have copied and pasted the email addresses to a new column...and had a coffee and some food.

Yes but that isn't productive given I'll probably need to do the same procedure in the future.
I'd rather spend some time finding out a more effective way to do it now.
But thank you for the immensely helpful post.

_Andy_

Strider590 wrote: »

No it works seriously, try it again lol

Paste the list with the spaces replaced, back to excel from notepad then select that new column and do the replace *ZZ with nothing.

Only using ZZ because unless you have "Buzz Lightyear" on your email list, nobody will have ZZ in the name

* is the wildcard function, by the way.

In Notepad it's replacing the space between first name and surname with ZZ. But not the space between surname and email, which is strange.

_Andy_

Right I think the problem in both Excel and Notepad is that it's not recognising the space between surname and email address as a space. This would explain why the delimiting suggestion isn't working.
Any ideas why this would be?
PS I can't post the actual data due to DPA.

Thanks

kevsan

if its not a space not sure what character it would be.

if you highlight the 'space' in excel and then copy and paste into the other field option on the delimited options page (where you selected space originally).

kevsan

alternatively as a quick and dirty fix, download and install textpad (https://www.textpad.com) and open the file in that, then use the block select mode (under configure menu) and you can highlight the block of text with emails in and copy & paste to a new file.

Not a fix as such, but would allow you to get the data.

Zazen999

_Andy_ wrote: »

Yes but that isn't productive given I'll probably need to do the same procedure in the future.
I'd rather spend some time finding out a more effective way to do it now.
But thank you for the immensely helpful post.

I take it you have done it then???

Why not just put all 3 pieces of info in different columns in the first place?

No offence, but it's been quite an inefficient way so far.....