+ Reply to Thread
Results 1 to 7 of 7

Extract each substring from current cell

  1. #1
    Registered User
    Join Date
    02-15-2010
    Location
    Turkey
    MS-Off Ver
    Excel 2007
    Posts
    3

    Extract each substring from current cell

    Hi friends,

    A1 cell data is : Microsoft (MS) Google (G) Yahoo (Yho)

    I want to extract each words who start with ( and end with ).

    B1 will be (MS)
    C1 will be (G)
    D1 will be (Yho)

    How can I do that?

    Thank you

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,575

    Re: Extract each sub string from current cell

    Here are two solutions. The first requires "helper" cells. It's more efficient but if you don't care for that you can construct "megaformulas" as shown in the second solution.
    Attached Files Attached Files
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Valued Forum Contributor mdbct's Avatar
    Join Date
    11-11-2005
    Location
    CT
    MS-Off Ver
    2003 & 2007
    Posts
    848

    Re: Extract each sub string from current cell

    Try this one:
    Please Login or Register  to view this content.

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Extract each sub string from current cell

    Another single cell alternative perhaps:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-15-2010
    Location
    Turkey
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Extract each sub string from current cell

    Quote Originally Posted by 6StringJazzer View Post
    Here are two solutions. The first requires "helper" cells. It's more efficient but if you don't care for that you can construct "megaformulas" as shown in the second solution.
    Dear 6StringJazzer,

    Complex one is good for me But when I look your example it couldn't found 3rd (Yho)

    Thanks for all friends for their suggestions

  6. #6
    Registered User
    Join Date
    02-15-2010
    Location
    Turkey
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Extract each substring from current cell

    For last word I used
    =RIGHT(A2,LEN(A2)-FIND("^^",SUBSTITUTE(A2,"(","^^",LEN(A2)-LEN(SUBSTITUTE(A2,"(",""))))+1)

    But still don't like this solution because if I have more than 4 parantheses it will crash

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,575

    Re: Extract each substring from current cell

    I had an error, which is corrected in the attached but the formula becomes very unwieldy compared to the ones above.

    I have also added a UDF here if you care to use one, also in the attached.

    Please Login or Register  to view this content.
    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)

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