+ Reply to Thread
Results 1 to 9 of 9

Cut the data after the comma from one column and paste it to new column automatically?

  1. #1
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Cut the data after the comma from one column and paste it to new column automatically?

    Hi guys, I have a bit of a problem I wonder if I can do this with a few clicks instead of this tedious manual method.

    I have a column of data with first name, last name, separated by a comma. There are 5000 names to get through and right now I'm cutting the last name (after the comma) and then pasting to a new column. Basically I need the first name and last name to be in two columns rather than in the one column separated by a comma like this..

    Is there a way for excel to do this automatically for me?

    So for example i want:

    [Fred,Bloggs] - in the one column, to be turned into [Fred][Bloggs] - in two separate columns.

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Have you tried using from the Data Tab, Text to columns?

    Select your text.
    Click on the Data Tab, Text to columns
    Select Delimited
    Select the Comma as delimiter
    Click Finnish
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Maybe with formula

    =LEFT(A1,FIND(",",A1)-1)&"]"

    and

    ="["&RIGHT(A1,LEN(A1)-FIND(",",A1))


    Row\Col
    A
    B
    C
    1
    [Fred,Bloggs] [Fred] [Bloggs]
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by newdoverman View Post
    Have you tried using from the Data Tab, Text to columns?

    Select your text.
    Click on the Data Tab, Text to columns
    Select Delimited
    Select the Comma as delimiter
    Click Finnish
    I will give this a try in a moment newdoverman, thanks for the tip.

  5. #5
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by newdoverman View Post
    Have you tried using from the Data Tab, Text to columns?

    Select your text.
    Click on the Data Tab, Text to columns
    Select Delimited
    Select the Comma as delimiter
    Click Finnish
    I will give this a try in a moment Newdoverman, thanks for the tip.

  6. #6
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by AlKey View Post
    Maybe with formula

    =LEFT(A1,FIND(",",A1)-1)&"]"

    and

    ="["&RIGHT(A1,LEN(A1)-FIND(",",A1))


    Row\Col
    A
    B
    C
    1
    [Fred,Bloggs] [Fred] [Bloggs]
    Thanks for the formula Alkey. Actually I should mention that the brackets "[ ]" were just to show an example of the column borders (they aren't actually in the data) - would that mean I just need to delete them from the formula or would it be drastically changed?

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by Gorguruga View Post
    Thanks for the formula Alkey. Actually I should mention that the brackets "[ ]" were just to show an example of the column borders (they aren't actually in the data) - would that mean I just need to delete them from the formula or would it be drastically changed?
    Then why did you showed them?

    =LEFT(A1,FIND(",",A1)-1)

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

    without brackets

  8. #8
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by AlKey View Post
    Then why did you showed them?

    =LEFT(A1,FIND(",",A1)-1)

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

    without brackets
    Yes, sorry for showing the brackets because I'm new to the forum. I was using the brackets just to demonstrate the column borders. Thanks for the updated formula i'll give it a try.

  9. #9
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by AlKey View Post
    Then why did you showed them?

    =LEFT(A1,FIND(",",A1)-1)

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

    without brackets
    Yes, sorry for showing the brackets because I'm new to the forum. I was using the brackets just to demonstrate the column borders. Thanks for the updated formula i'll give it a try.

  10. #10
    Registered User
    Join Date
    01-05-2015
    Location
    London
    MS-Off Ver
    Excel
    Posts
    7

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    Quote Originally Posted by newdoverman View Post
    Have you tried using from the Data Tab, Text to columns?

    Select your text.
    Click on the Data Tab, Text to columns
    Select Delimited
    Select the Comma as delimiter
    Click Finnish
    Newdoverman, you are a genius! This worked straight away, thanks so muchyou literally just saved me a whole day's worth of work.

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Cut the data after the comma from one column and paste it to new column automatically?

    I'm glad to have helped. This built in function of Excel is very handy at times.

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

+ 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. Evaluating rows column-by-column to separate comma-separated data
    By adventurepirate in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-30-2014, 03:06 PM
  2. Replies: 2
    Last Post: 03-01-2013, 02:01 AM
  3. [SOLVED] Paste data automatically into the next available column
    By Tishmimi in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2012, 08:25 AM
  4. Macro to copy and paste special values for column data and filter column data
    By ascottbag in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-10-2012, 02:15 PM
  5. Replies: 3
    Last Post: 02-08-2010, 06:18 PM

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