Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Extract all text after , in a cell

  1. #1
    Board Regular
    Join Date
    Oct 2009
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract all text after , in a cell

    Hi all,

    I'm trying to extract all the text in a cell which is on the right side of a comma (,)

    Currently I am using this formula,

    =RIGHT(C1,FIND(",",C1)-1)

    However it sometimes is missing some of the data after the , and sometimes pulling a few characters before the ,

    any suggestions on how to correct my formula please?

    Thanks in advance.

  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi. Try

    =TRIM(RIGHT(C1,LEN(C1)-FIND(",",C1)))
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi VoG,

    many thanks for your help, I have 2 questions for you please.

    1- This copies the data but can it "take" the data (so it no longer exists in the original cell?
    2. if the cell has more then 1 x , can it simply take the data after the last ,?

    Thanks for your kind assistance.

  4. #4
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi.

    1. A formula cannot do that. You would need VBA.

    2. Try

    =TRIM(RIGHT(SUBSTITUTE(C1,",",REPT(" ",255)),255))
    HTH, Peter
    Please test any code on a copy of your workbook.

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi VoG,

    Many thanks for all your help. Instead of taking out the data I could adjust the formula to pull from the LEFT of the , as opposed to the right.

  6. #6
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract all text after , in a cell

    Try like this

    Sheet1

     CDE
    1abc, defg,hijkl, mnopmnopabc, defg,hijkl,

    Spreadsheet Formulas
    CellFormula
    D1=TRIM(RIGHT(SUBSTITUTE(C1,",",REPT(" ",255)),255))
    E1=SUBSTITUTE(C1,D1,"")


    Excel tables to the web >> Excel Jeanie HTML 4
    HTH, Peter
    Please test any code on a copy of your workbook.

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi VoG,

    Once again many thanks for your kind assistance.

  8. #8
    New Member
    Join Date
    Nov 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Extract all text after , in a cell

    Quote Originally Posted by VoG View Post
    Try like this

    Sheet1

    * C D E
    1 abc, defg,hijkl, mnop mnop abc, defg,hijkl,

    Spreadsheet Formulas
    Cell Formula
    D1 =TRIM(RIGHT(SUBSTITUTE(C1,",",REPT(" ",255)),255))
    E1 =SUBSTITUTE(C1,D1,"")


    Excel tables to the web >> Excel Jeanie HTML 4

    Thank you Sir JEE. This is very helpful for me.

  9. #9
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract all text after , in a cell

    Hi,

    I'm trying this on this string:

    39685~54790@110297535758561678@FULL@0.00@

    Sometimes the string may be:

    25364~39685~54790@110297535758561678@FULL@0.00@

    Trying to extract everything after the last "~" using:

    =TRIM(RIGHT(SUBSTITUTE(C1,"~",REPT(" ",255)),255))

    Excel says there's an error in the formula when I commit the cell. I'm using Excel 2010.

    Can anyone help? Also thanks in advance!
    Last edited by uncleplum; Aug 26th, 2015 at 07:31 AM. Reason: Making it more polite

  10. #10
    New Member
    Join Date
    Aug 2015
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract all text after , in a cell

    Quote Originally Posted by uncleplum View Post
    Hi,


    Excel says there's an error in the formula when I commit the cell. I'm using Excel 2010.

    My error.. my list separator was set to ";". Changed back to "," and all is good. Thank you anyway!

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
  •