+ Reply to Thread
Results 1 to 18 of 18

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

  1. #1
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

    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

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    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
    HTH
    Regards, Jeff

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    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!!
    Last edited by oeldere; 09-03-2013 at 07:00 PM.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    Thanks oeldere, worth an initial shot

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    Of course Jeffrey, but we wait an see.

  6. #6
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

    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.
    Last edited by jachbo; 09-04-2013 at 12:20 AM. Reason: added example of spreadsheet

  7. #7
    Registered User
    Join Date
    04-28-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2007
    Posts
    50

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

    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
    Attached Files Attached Files

  8. #8
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

    @moppyau

    try you solution with this emailadres

    jaol@yahoo.com

    It get the result in both columns

  9. #9
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

    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.

  10. #10
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,958

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

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

    And after that delete column A,

  11. #11
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

    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

  12. #12
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    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

  13. #13
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

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

  14. #14
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    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?

  15. #15
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

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

  16. #16
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    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
    a@primenet.com
    a@primenet.com
    3
    b@hotmail.com
    b@hotmail.com
    4
    c@yahoo.com
    c@yahoo.com
    5
    d@MSN.com
    d@MSN.com
    6
    e@Gmail.com
    e@Gmail.com
    7
    f@AOL.com
    f@AOL.com
    8
    g@american-hi-tek.com
    g@american-hi-tek.com
    9
    h@aol.com
    h@aol.com

  17. #17
    Registered User
    Join Date
    09-03-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2010 - Microsoft Professional Plus 2010
    Posts
    7

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

    Yep that worked and you are awesome for helping me.

  18. #18
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,179

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

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Trying to move specific range to a specific sheet based on data in certain column
    By cain2011 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-01-2013, 08:09 AM
  2. Move row of data to another sheet once a date is entered in a specific column
    By stankanie in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-27-2012, 09:21 AM
  3. Move Specific Data One column to another column
    By traderindia in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-06-2010, 07:02 AM
  4. Copy/move a word from a cell in a column to another column and autofill
    By excelaspire0219 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-05-2009, 03:54 PM
  5. [SOLVED] How to move data in Column A into seperate columns / rows
    By iain.sanderson@wanadoo.fr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2006, 11:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1