+ Reply to Thread
Results 1 to 21 of 21

Extract data between commas

  1. #1
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Extract data between commas

    Hi, i would like to extract data between commas and display them in a cell. And if they have a number inside a bracket to break them up multiple times (dependent on their number)

    For example in Cell A1 = Kevin Day, Peter Jones (2), Nathan Quinn, Luke Daly (3)

    Cell A2 = Kevin Day
    Cell A3 = Peter Jones
    Cell A4 = Peter Jones
    Cell A5 = Nathan Quinn
    Cell A6 = Luke Day
    Cell A7 = Luke Daly
    Cell A8 - Luke Daly

    Any help is much appreciated.

    Regards,

  2. #2
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Extract data between commas

    Not elegant formula but try this, put this on A2 and copied down until blanks

    =IFERROR(LEFT(TRIM(MID(SUBSTITUTE($A$1,", ",REPT(" ",100)),(ROWS($A$1:A1)-1)*100+1,50)),SEARCH("(",TRIM(MID(SUBSTITUTE($A$1,", ",REPT(" ",100)),(ROWS($A$1:A1)-1)*100+1,50)),1)-1),TRIM(MID(SUBSTITUTE($A$1,", ",REPT(" ",100)),(ROWS($A$1:A1)-1)*100+1,50)))
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Azumi,

    Thank you for the response, but it is not quite right:
    1) Sorry to be a pain, but i realised it needs to go across columns, not down rows: For example: in Cell A1 = Kevin Day, Peter Jones (2), Nathan Quinn, Luke Daly (3)

    Cell B1 = Kevin Day; Cell C1 = Peter Jones; Cell D1 = Peter Jones; Cell E1 = Nathan Quinn; Cell F1 = Luke Day; Cell G1 = Luke Daly; Cell H1 - Luke Daly

    2) the number is brackets needs to somehow count that by the number of times it is added to a cell.

    Hope this makes sense. Again appreciate your help.

    Regards,

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Extract data between commas

    Can you attach a workbook that shows the before and after data mocked up?

  5. #5
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    See the attachment.

    A2 is the data entered. B1 to H1 is the data extracted between the commas and taking into account the number inside the bracket.
    Attached Files Attached Files

  6. #6
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Extract data between commas

    Do you have more sample data than a single cell? Something that is representative of most if not all cases possible?

  7. #7
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Try this one. Does this help?
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Do you need more information?

  9. #9
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Extract data between commas

    Here's a VBA solution that does what you are looking for I believe. Let me know.

    The attached workbook includes this macro.

    Please Login or Register  to view this content.

  10. #10
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Extract data between commas

    Or try UDF.

    Please Login or Register  to view this content.
    Use: =SplitName($A2,COLUMNS($A2:A2))
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Thanks guys...that works...But now I am going to challenge you even more..I actually need this to work in Googlesheets. Is there another solution??

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract data between commas

    I've been able to do it with formulas, but it requires 3 helper rows and some copy/paste. So it's rather awkward. Not nearly as clean as the VBA offered.

    Also I am not all that familiar with Googlesheets. From what I've seen most Excel functions seem to work there though.

    Please let me know. I'll upload and explain.
    Dave

  13. #13
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Extract data between commas

    I actually need this to work in Googlesheets.
    Is this a one-off conversion or will it need to be done regularly? If it's a one-off then you could run one of the above solutions in Excel then copy/paste the output to GoogleSheets.
    If you really need a GoogleSheets solution then I'm out - I have zero knowledge of GoogleSheets. Maybe Dave's offer above is your best option so far.

  14. #14
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Dave, thanks for this, Yes, please upload and i am happy to see ho wit works. Many thanks.

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract data between commas

    Please try at B2:L16, this also work with google sheet.

    =IFERROR(INDEX(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2,",",REPT(" ",200)),COLUMN($A1:$L1)*200-199,200),"(",REPT(" ",200)),200)),MATCH(0,INDEX(N(COUNTIF($A2:A2,TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2,",",REPT(" ",200)),COLUMN($A1:$L1)*200-199,200),"(",REPT(" ",200)),200)))=--TEXT(LEFT(RIGHT(TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",200)),COLUMN($A1:$L1)*200-199,200)),2)),"0;;;\1")),),)),"")
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Extract data between commas

    In B2 then drag across
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 05-25-2019 at 08:18 AM.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  17. #17
    Spammer
    Join Date
    01-08-2015
    Location
    Cali, Colombia
    MS-Off Ver
    365
    Posts
    302

    Re: Extract data between commas

    Hi, to all!

    Another option could be:
    =TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE(","&$A2,",",REPT(" ",200)),200*COLUMNS($B2:B2),200),"(",REPT(" ",200)),200))

    Blessings!
    Last edited by johnmpl; 05-25-2019 at 08:23 AM.

  18. #18
    Forum Contributor
    Join Date
    08-19-2012
    Location
    Sydney
    MS-Off Ver
    MS Office 365/16
    Posts
    282

    Re: Extract data between commas

    Hi Bo_Ry,

    What you did was amazing. Thank you. I now have one simple problem, which i am sure is easy for you, but not for me. The actual data from my real document is located in different cells. I have uploaded the latest version. I am sure there is a simple change in the formula to account for the change of cell location.

    Data = cell H3

    First name - Cell AE3 and so on
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Extract data between commas

    Pl see my previous post.

  20. #20
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extract data between commas

    Hi Sirdon,

    Please try at AE3
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    COLUMN($A3:$Z3) don't change as it needs to produce running number start from 1..26

    SUBSTITUTE($H3 change to your data cell

    COUNTIF($AD3:AD3 change to 1 cell on the left before the First name (AE3)


    Could you please reply to everyone who spends their time to helps you?


    Hi kvsrinivasamurthy,

    The number behide each name is use for repeat when extract name
    Eg: Peter Jones (2), extract Peter Jones 2 times.
    Attached Files Attached Files

  21. #21
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract data between commas

    @ sirdon

    Here it is. Sorry for the delay. It's a 3 day holiday weekend in the US.

    In the attached column A this formula repeats source data 4 times each.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    B10:P10 parses each paired name/count.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    In B11:P11 returns the number of times each name repeats.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    B12:P12 is a cumulative sum of the counts.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    B13:P13 is the final output.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then copy B10:P13 and paste into B14, B18, B22 and B26.
    Attached Files Attached Files

+ 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. Remove numerous trailing commas from text string that contains commas
    By JenSven1 in forum Outlook Programming / VBA / Macros
    Replies: 2
    Last Post: 05-23-2016, 10:19 PM
  2. Replies: 1
    Last Post: 05-23-2016, 11:05 AM
  3. [SOLVED] Extract text between two commas
    By TestMailinator in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-02-2015, 06:20 AM
  4. Replies: 5
    Last Post: 01-17-2014, 05:56 PM
  5. Replies: 4
    Last Post: 06-21-2012, 08:17 PM
  6. Replies: 3
    Last Post: 05-30-2012, 04:28 PM
  7. [SOLVED] help commas before data
    By James in forum Excel General
    Replies: 5
    Last Post: 03-08-2005, 03:06 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