Thanks Thanks:  0
Likes Likes:  0
Page 1 of 5 123 ... LastLast
Results 1 to 10 of 47

Thread: Copy text after last comma in cell

  1. #1
    New Member
    Join Date
    Nov 2009
    Location
    Redding Ca
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Copy text after last comma in cell

    I am using excel 2007, I have a column of cells that contain text that is separated by commas. Each cell may have 3 commas or 5 or 2 commas. What I need to do is copy the last text after the last comma in the cell.

    example
    xxx,yyy,zzz copy to another cell the text zzz
    xxx,yyy copy to another cell the text yyy
    xxx,yyy,zzz,aaa copy to another cell the text aaa


  2. #2
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    try this

    Sheet2

     AB
    2XXX,YYY,ZZZZZZ
    3xxx,yyyyyy
    4xxx,yyy,zzz,aaaaaa

    Spreadsheet Formulas
    CellFormula
    B2=MID(A2,FIND("|",SUBSTITUTE(A2,",","|",LEN(A2)-LEN(SUBSTITUTE(A2,",",""))))+1,3)
    B3=MID(A3,FIND("|",SUBSTITUTE(A3,",","|",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))))+1,3)
    B4=MID(A4,FIND("|",SUBSTITUTE(A4,",","|",LEN(A4)-LEN(SUBSTITUTE(A4,",",""))))+1,3)


    Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Welcome to the board...

    Perhaps

    =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

    Hope that helps.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Nov 2009
    Location
    Redding Ca
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Reply to texasalynn
    That worked great but it is only copying the first 3 chars after the last comma.

  5. #5
    New Member
    Join Date
    Nov 2009
    Location
    Redding Ca
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    OK the jonmo1 worked perfect, it took all characters no matter how many commas are in the string.

  6. #6
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    7,311
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by jonmo1 View Post
    Welcome to the board...

    Perhaps

    =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1)))

    Hope that helps.

    Ouch! That is so clever! Bravo!

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by Special-K99 View Post
    Ouch! That is so clever! Bravo!
    Learned it right here on this forum...Can't remember for sure who I got it from, probably Aladin...
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    New Member
    Join Date
    Nov 2009
    Location
    Redding Ca
    Posts
    45
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Well I search tese forums and if I can't find it and end up asking. everybody is so responsive and supportive here.

  9. #9
    Board Regular texasalynn's Avatar
    Join Date
    May 2002
    Location
    Houston, TX
    Posts
    8,457
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    Quote Originally Posted by Juleew View Post
    Reply to texasalynn
    That worked great but it is only copying the first 3 chars after the last comma.

    it looked like from your example that was what you wanted. But glad that you found a solution.

  10. #10
    Board Regular
    Join Date
    Aug 2008
    Posts
    132
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Copy text after last comma in cell

    I have a similar issue. I am trying to copy text from one cell to another. The first cell contains a very long text string with the information separated by commas and enclosed by quotation marks. How do I pick and choose the text within the quotations I would like to extract? I would like to achieve this using a formula.

    Example of A1:
    "Internal/External Agent Calls Report","Report data from: Saturday, March 22, 2014 to Saturday, March 29, 2014","Group By","Outbound Complete","Outbound InComplete","Internal Complete","Internal Incomplete"," # - Name","Number of Calls","Average Duration","Number of Calls","Average Duration","Number of Calls","Average Duration","Number of Calls","Average Duration",,,,,,,,,,,"Filters Applied:","Relative Date:",,"Agent Group Number:","Enterprise Group ID(s):",,"71","Agent Number:","30000-39999","Display Level:","Sort Order:","Enterprise Group, Agent Group, Agent, Call Detail","by ID","Start Time:","2014-03-22","Start Time:","End Date:","00:00:00","2014-03-29","End Time:","23:59:59","Time Zone:","LOCAL","38,39,40,41,45,46","Dialed Digits: ","200",," Tuesday, April 15, 2014 15:41:42","Page -1 of 1"

    Example of output I would like:
    A2
    Report data from: Saturday, March 22, 2014 to Saturday, March 29, 2014
    A3
    Sort Order:
    A4
    Dialed Digits:
    A5
    200

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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