How to split 2 or more email address in 1 cell?

[ru5ty]

Hi,

I need help to split 2 or more email address in 1 cell (Excel) to a new cell.
What is the formula for it?

Example: A1 contain "abc@yyyyy.comtty@zzzzz.com
Result: B1 contain "abc@yyyyy.com"
C1 contain "tty@zzzzz.com"

Can anyone help me please.

ru5ty

[Sisyphus]

Hi,
Take a pragmatic approach.

1. There is no Excel formula that can write to another cell but the one it occupies. Therefore you should pick one of these options:
A) you place a formula in column B that extracts the first address, another formula in C that gets the 2nd address, and so on. If there is only one address in A the cell in C shows nothing, but the formula is there. If there are 3 addresses in A but there is no formula in D to extract it you will never know it was there.
B) let a macro do the job. This means writing VB code.

2. Regardless of the means you choose, your data need to have something to tell the formula or program where one address ends and the next one starts. Perhaps there are line feeds or carriage returns, a space, comma or semicolon can't be found in an address. So such a character can be used to determine the end of an address. If you don't have any .net, .com.au, .gov etc. In your data you might determine that ".com" is the end of an address.
When using code it is easier to accommodate several conditions. Putting a thought like "if you find '.com' or '.gov' or '.org'" into a worksheet formula will take a lot of formula. So, you should also consider how addresses you have, seeing that you will need more formulas than you have addresses.

I haven't thought about which formula might do the job. In code the job wouldn't be half as difficult for me. But there are others on the web who may find a solution once they know the problem. So, tell us how your data are constructed. Perhaps you can post an actual sample. You can x-out all the names. What I like to look at is what is between them and, perhaps, at the end of them. That is where you'll find the jokers.
regards,

[ru5ty]

Hi Sisyphus,

Thank you for your inputs.

Honestly speaking, I know nothing about VB, which leave me with no choice.
As for the addresses, what I notice is the input varies, example xxxxx@xxxx.comxxxxx@xxx.net, or xxxxx@xxxx.com/xxxxx@xxx.net, or xxxxx@xxxx.net xxxxx@xxx.net. I have no issue with one address in one cell.
My problem is I need to single out those cell that contain more than 1 email address from a records of more than 100,000.

There may not be a solution, but I do appreciate the inputs.

Thank you once again and anyone that drops in.

ru5ty

[Sisyphus]

Hi,
That looks very different from what I understood before! Easier, too.
I think you only need to count the addresses in each cell. No need to extract them, right?
The sample you give suggests two possiblities:
1. Count commas. But for this you need to be sure that it is always a comma being used to separate addresses.
2. Count the @ sign. Every address must have one, and no address can have two. In this way you will not know if any of the addresses are incomplete, but that isn't the task - yet.

So, do we start thinking about inserting a column in the worksheet, say, next to the column containing the cells to be examined and putting a formula in it that returns the number of @ found in each cell -1 (presuming that most will have only one which isn't a problem)?

This sounds easy but you have to multiply by 100,000 you say. There are some 64,000 rows in a worksheet. Even to copy a formula to them will take a long time. You probably also need to make a copy of your data. Or are the originals in Excel? Worksheets of 50.000 rows are unwieldy. But if you make sheets of 1000 rows there are a hundred of them. You may like some automation to help in either process. But by the time you got the preparation of the data to be examined automated you could have identified the odd ones among them in situ. It looks like you can spend your time copying data and formulas or learning a little code.

What are you going to do after you have that column I mentioned above. How about, if there are 2000 odd ones - only 2%. That seems to be your estimate. You thought of separating out the addresses. What do you want after that? Insert them back into the original sheet in separate lines? Will you want to keep one and discard the others? You may reach the end faster if you define it from the outset.

Let me know how I can help.
Have a pleasant day!

[ru5ty]

Ok, let assume A1 have a record xxxx@xxxxx.comxxxx@xxxx.net.
In this A1 cell, it contain 2 email address.
I have try this formula =COUNTIF(A1,"*@*"), it return 1. If my formula is correct, the result I expect should return 2, rite?

So i guess my formula is not rite.

What should be the correct formula?

[Sisyphus]

Hi,

The following formula will return a number, either 1 or 2 after examining cell $A4.

Code: Select all

=IF( ISERROR(SEARCH("@",RIGHT($A4, LEN($A4) - SEARCH("@",$A4,1)),1)),1,2)


It builds on SEARCH returning an error if the targeted string, in this case "@" isn't found. There are two SEARCH functions and, therefore, two chances of returning an error. I have constructed the formula to only deal with the second. Therefore, you will get the same result regardless of where the error occurred. In plain text that means that the formula will return 1 if there is an error or 2 if there is none.
1 = There is not more than 1 address in $A4, maybe none
2 = There are at least 2 addresses in $A4, maybe more

If you aren't scared of a slightly longer formula, take this improvement:

Code: Select all

=IF( ISERROR(SEARCH("@",RIGHT($A5, LEN($A5) - SEARCH("@",$A5,1)),1)),IF(ISERROR(SEARCH("@",$A5,1)),0,1),2)


It will return a number between 0 and 2 where 0 and 1 are actual counts while 2 is a minimum. You may like to use the demonstrated system to extend the count to 3 or more. You can copy the second formula one line up to change the examined cell to $A4.
I hope that helps, and apologize for having asked too many questions.
Regards!

[launcher]

Do not help a spammer maintain its database of 100,000s! Just let them do their own work if they want to SPAM good people. Is there really a legitimate purpose to having 100,000+ e-mails in an easy-to-access formatted report? If so, let me know and I will apologize. .

[Don]

This person could be a spammer or they could be someone that needs this for a legitimate reason. Some of us have done work for people maintaining large databases of customers or employees for international companies and sometimes they need to manage large lists of data such as emails. It's always difficult to tell why someone needs something but there are a lot of people who need similar help on the web.


Rusty, are you still looking for a solution or did you get what you needed? Also, if you're going to be doing a lot of this type of data manipulation in the future, you really should learn VBA for Excel because that would make your life so much easier.

[ru5ty]

Hi, If you think that I am spamming, I apologise for it.
Well I am working in an Brokerage Operations. Recently receive a project from my boss to generate letter and email to our clients. What I have encounter is that some clients in our database consist of more than one email addresses, that 's why I have to resort to asking for help from the net and the information downloaded to me is in excel format.

If you guys think that I am a spammer, I shall not copy the formula provided. I will work my way out.

Thanks for all the input.

Ru5ty

[ru5ty]

Hello Sisyphus,

Thank you for your effort and time, really appreciate that.

Have a nice day.

Regards.
Ru5ty