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

charlie pink - tls79@xxx.com
elvies king - tls78@zzz.de

<tbody>
</tbody>

lewis purple - slt80@fff.co.uk

<tbody>
</tbody>

and here what i would like to have

tls79@xxx.com tls78@zzz.de <tbody> </tbody> slt80@fff.co.uk <tbody> </tbody>

<tbody>
</tbody>

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

 

[XOR LX]

Hi,

Is the part that you wish to extract always preceded by a dash (-)? In any given string, will there only ever be one such dash?

Regards

 

[apo]

If your values that you have are in Column A (starting in A1)..

This will put the put the name in column B and the email address in Column C..

Private Sub CommandButton1_Click()
Dim x, i
For Each i In Range("A1").CurrentRegion
x = Split(i, "-")
i.Offset(, 1).Resize(1, 2).Value = Array(x(0), x(1))
Next i
End Sub

If your column structure is different and you can't modify it.. let me know..

 

[TLS79]

Hi,

Is the part that you wish to extract always preceded by a dash (-)? In any given string, will there only ever be one such dash?

Regards

Hi thanks for getting back to me!

there will be allways this string! but some email addresses could have also a - because i tried the function test to columns.
and it did not work!

thanks

 

[TLS79]

1

 

[TLS79]

If your values that you have are in Column A (starting in A1)..

This will put the put the name in column B and the email address in Column C..

Private Sub CommandButton1_Click()
Dim x, i
For Each i In Range("A1").CurrentRegion
x = Split(i, "-")
i.Offset(, 1).Resize(1, 2).Value = Array(x(0), x(1))
Next i
End Sub

If your column structure is different and you can't modify it.. let me know..

Sounds good, but i have no idea where to put it? is it a macro?! I am just a standard user who tries to make the next month easier! But i would be happy if you can point me in the right direction

the first field would be b2 and c2 for the name and c3 for the email.

and actully i would not even need the name only the email is important. but if its better with name and email no problem to delet this cullom is easy

thanks for your help!

 

[Jonmo1]

Try

=REPACE(A1,1,FIND("-")+1,"")

 

[TLS79]

Try

=REPACE(A1,1,FIND("-")+1,"")

Hi thanks

i had to change it a bit , i used yours but there came an error "youve entered too few arguments for this function"

instead of yours i used =replace(a1,1,find("-"a1)+1,"")

can you explain this formular for me? replace is ok, a1 (is what/where,) but was is the 1 and the +1 at the end? whould be great if i could understand what i have done!

 

[TrumpExcel]

Try this formula

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Note:
1. This assumes that there is always a space before the email id
2. Email id is always at the end

 

[Jonmo1]

Good catch, the formula should have been
=REPACE(A1,1,FIND("-",A1)+1,"")

The 1 is the beginning character of what to replace
The +1 accounts for the space AFTER the -

Syntax is
=REPLACE(String, BeginningPosition# ,HowManyCharacters, StringToReplaceWith)

So if A1 is "charlie pink - tls79@xxx.com"
FIND("=",A1) = 14 (the - is in the 14th postion.

So the formula becomes
=REPLACE(charlie pink - tls79@xxx.com, 1 ,14+1, "")
=REPLACE(charlie pink - tls79@xxx.com, 1 ,15, "")

So it replaces 15 characters, beginning with 1, with "" - or nothing..

Hope that helps.