Seperate data in column A that has specific word and move to column B or C or D

**jachbo** · 09-03-2013, 06:50 PM

So much for taking an Excel class, I am at a loss.

I have a list of 30,000 email addresses. All are in column A.

I want to tell Excel to:

move all hotmail.com addresses to column B;
move all Yahoo.com addresses to Column C;
move all MSN.com addresses to Column D;
move all gmail.com addresses to Column E;

and so on...

I need someone to keep it simple for me because looking through the forum my head is spinning. I need to know exactly what to post as the formula? Macro?

Thanks to anyone who has an answer.

Candi

**jeffreybrown** · 09-03-2013, 06:58 PM

Hi Candi & Welcome to the Forum,

Try this...

In B2 copied down and to the right >> =IF(ISNUMBER(SEARCH(B$1,$A2)),$A2,"")

Using row 1 to contain the keywords to return...

Data Range

	A	B	C	D	E
1		Hotmail	Yahoo	MSN	gmail
2	Hotmail.com	Hotmail.com
3	Yahoo.com		Yahoo.com
4	MSN.com			MSN.com
5	gmail.com				gmail.com

**oeldere** · 09-03-2013, 06:58 PM

You get better help if you follow the advice in the link below.

http://www.excelforum.com/the-water-...-question.html

@ jeffreybrown

I bet there is text before the given data!!

**jeffreybrown** · 09-03-2013, 07:01 PM

Thanks oeldere, worth an initial shot

**oeldere** · 09-03-2013, 07:04 PM

Of course Jeffrey, but we wait an see.

**jachbo** · 09-04-2013, 12:12 AM

WOW, I must be really stupid or just tired, where do I put that formula. I added the heading in row 1 like you said but....

Do I put it in all 30,000 cells for each column? Is that what you meant by down and over?

forum example.PNG

OK that is supposed to be an image, I have no idea how to paste a few lines of the spreadsheet itself. Tried table but it made no sense to me either.

I may be the stupidest person you have tried to help.

**moppyau** · 09-04-2013, 12:57 AM

Hi Candi,
•Copy the cell that contains the original formula to the clipboard. (B2)
•Click into the "cell name" box (in the upper left) and enter the range that you want to select, followed by Enter. (B2:F10000 for example)
•Paste to the selection.
I've attached a small example

**oeldere** · 09-04-2013, 01:56 AM

@moppyau

try you solution with this emailadres

[email protected]

It get the result in both columns

**jachbo** · 09-04-2013, 12:42 PM

OK, that worked and thank you so much. I do have a problem though, when I said move to the other columns I also wanted it removed from column A. Also, how do I give y'all stars.

**oeldere** · 09-04-2013, 03:04 PM

After you used the formula's => copy => paste special => values.

And after that delete column A,

**jachbo** · 09-04-2013, 09:35 PM

I can't because their is still data in column A that I need I just had to separate those specific emails but their are corporate emails left that I need from many different URLS

**jeffreybrown** · 09-04-2013, 09:42 PM

Hi Candi,

I think it would be best if you attach a sample workbook with a before and after so we know exact requirements!!

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**jachbo** · 09-05-2013, 12:28 PM

OK, here it is and once again thank you for your help it is really appreciated. example.xlsx

**jeffreybrown** · 09-05-2013, 12:39 PM

Hi Candi,

What you are asking seems it can only be done via VBA. A formula cannot delete a string from a cell.

Do you want to do VBA?

**jachbo** · 09-05-2013, 01:56 PM

I am not sure what a VBA is but if you think it will work then yes.

**jeffreybrown** · 09-05-2013, 06:03 PM

I think this should work. I can't figure out a smart way to combine the two so we can just go with an extra column to grab the corporate e-mails.

In G2 copied down >> =IF(COUNTIF(B2:F2,"?*")=0,A2,"")

Just use the other formula for B2:F9.

Hope this will work for you?

Data Range

	A	B	C	D	E	F	G
1		Hotmail	Yahoo	MSN	Gmail	AOL	corporate
2	[email protected]						[email protected]
3	[email protected]	[email protected]
4	[email protected]		[email protected]
5	[email protected]			[email protected]
6	[email protected]				[email protected]
7	[email protected]					[email protected]
8	[email protected]						[email protected]
9	[email protected]					[email protected]

**jachbo** · 09-05-2013, 08:18 PM

Yep that worked and you are awesome for helping me.

**jeffreybrown** · 09-05-2013, 09:08 PM

You're welcome…glad you have a workable solution and thanks for the feedback...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Seperate data in column A that has specific word and move to column B or C or D

LinkBack

Thread Tools

Rate This Thread

Display

Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Re: Seperate data in column A that has specific word and move to column B or C or D

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to move specific range to a specific sheet based on data in certain column

Move row of data to another sheet once a date is entered in a specific column

Move Specific Data One column to another column

Copy/move a word from a cell in a column to another column and autofill

[SOLVED] How to move data in Column A into seperate columns / rows

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions