Excel advanced help needed
Discussion
I have a long list of email addresses in Excel. I wish to replace the beginning part of all the addresses with a certain string, i.e. I want to replace the text up to the @ part of the addresses.
How can I do this in Excel? I am assuming I will need to use the replace function using VB but I've never been too good with it myself.
How can I do this in Excel? I am assuming I will need to use the replace function using VB but I've never been too good with it myself.
It looks like FIND, and LEFT (and/or RIGHT) will sort you out.
Get the position of the @ symbol (assuming there's only one), then get concatenate your string and the RIGHTmost part of the email address.
You may need to use the LEN function as well.
These are all worksheet functions. No need for VBA.
Having just typed all that, it turns out there is a REPLACE function that may do what you want.
As always Excel is all about knowing the cookbook inside out.
Gah. Too slow. (And less helpful)
Get the position of the @ symbol (assuming there's only one), then get concatenate your string and the RIGHTmost part of the email address.
You may need to use the LEN function as well.
These are all worksheet functions. No need for VBA.
Having just typed all that, it turns out there is a REPLACE function that may do what you want.
As always Excel is all about knowing the cookbook inside out.
Gah. Too slow. (And less helpful)
Edited by bitwrx on Thursday 8th April 11:21
Jinx said:
Aviz said:
A quick and dirty way is to copy the list into notepad, save as txt.
Import back into excel, using @ as the delimiter. ?
You could always use the "text to columns" function to do this.Import back into excel, using @ as the delimiter. ?
I'm definitely not an excel expert, but i have done exactly what the OP is asking for before using my dirty, inneficient way
Liszt said:
Col A|| Col B|| Gives||
Excellent!| Somebody@company.com | =MID(A3,1,(FIND("@",A3))-1) | Somebody |
| Somebody@company.com | =MID(A4,(FIND("@",A4))+1,1000) | company.com |
| Somebody@company.com | =MID(A5,(FIND("@",A5)),1000) | @company.com |
I've now got a list of all the emails with the second half of the address, i.e. @company.com
How do I add something to the beginning of all of them, i.e. info?
Liszt said:
=CONCATENATE("Info",MID(A5,(FIND("@",A5)),1000))
Thank you!Liszt said:
Hmm. Your not setting up a spam list are you? Feels like I have just handed over a loaded gun
Its an old subscribed list we had lying around, but it seems as though many of the name specific emails are no longer in use. Certainly not spam though.Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff