how to extract content (maybe find and replace)

[TLS79]

Hi folks,

Have an issue which bothers me for a year now, I receive every month a list with data from our website, users who registered
in one of the cells I have the name and the email address but i need it separated

here are an example of it what i have
[TABLE="width: 750"]
<tbody>[TR]
[TD]charlie pink - tls79@xxx.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]elvies king - tls78@zzz.de
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 750"]
<tbody>[TR]
[TD]lewis purple - slt80@fff.co.uk[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

and here what i would like to have
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 750"]
<tbody>[TR]
[TD]tls79@xxx.com[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tls78@zzz.de
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 750"]
<tbody>[TR]
[TD]slt80@fff.co.uk[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

any ideas, your help is much appreciated as I do it at the moment manually and we talking about list from 2500 users

 

[Rick Rothstein]

If your email addresses are always at the end of your text, then that means you always have a space between the dash and the first character of the email address (otherwise the dash would be considered part of the email address as it is a valid email character), so you can use these formulas...

A1: charlie pink - tls79@xxx.com

B1: =LEFT(A1,LEN(A1)-LEN(C1)-3)

C1: =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))

If you only want the email address and do not care about the "full name in front of the dash" part, then just use the formula in C1.

 

[TLS79]

There been super suggestions and 3 of them even working! HUHU

but i know everyone is killing me know, and I will start running away at the moment I push the button post!

no that i have written the email address in this field! is there a quick way to remove the formel and keep the email address, that i can convert it to a hyperlink! ( i know how to convert it to a hyperlink) but at is an formular and not text, sound tricky)



and i am running

 

[Jonmo1]

You can copy the cell with the formula
Then right click the same cell, Paste Special - Values.

 

[TLS79]

ok forget it found it copy and paste values only,


Thanks guys we can close this thread,

who ever from you guys who gave me an answer comes to london, i will pay a pint!