+ Reply to Thread
Results 1 to 14 of 14

How to extract a string within brackets repeatedly

  1. #1
    Registered User
    Join Date
    03-03-2006
    Posts
    27

    How to extract a string within brackets repeatedly

    I have the following information in a cell. I want to get the exchange:ticker which is inside the brackets to be returned (coma delimited). How can I do this? One thing to note here that the number of companies in the cell can vary

    Banco Bilbao Vizcaya Argentaria (CATS:BBVA); Banco Santander, S.A. (CATS:SAN); Banesto Banco Espanol de Credito SA (CATS:BTO); Banco Popular Espanol SA (CATS:POP); Caja de Ahorros y Monte de Piedad de Madrid; La Caixa Group, Asset Management Arm

  2. #2
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Hi

    Assuming that text in A1 (all of it) then try the following in B1 copied across columns (note it won't work properly if you have duplicated ticker values in A1):

    =MID($A1,FIND("(",$A1,FIND(A1,$A1))+1,FIND(")",$A1,FIND("(",$A1,FIND(A1,$A1))+1)-FIND("(",$A1,FIND(A1,$A1))-1)

    Richard
    Richard Schollar
    Microsoft MVP - Excel

  3. #3
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    Thanks Richard. This works for the first instance but doesn't give me the rest of the text. For the above example I woud like to get back

    CATS:BBVA, CATS:SAN, CATS:BTO, CATS:POP.

  4. #4
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    That's why I said copy across columns - each code will be in a separate column (or an error value once codes have been exhausted). If you want them all in a single cell then I think you are either looking at using an add-in such as morefunc or a VBA code solution.

    Richard

  5. #5
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    Thanks Richard, no disrespect, but I'm sure there is a way to do this since I had done this before but can't recall now how I did it. I didn't use any macro or addins

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Tex to Coumns VBA

    Try this out
    Please Login or Register  to view this content.
    See attached
    TextToColumns.xls

  7. #7
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    Thanks Dave, that helps. But isn't there a way this can be done without using any macros?

  8. #8
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    Will you always know how many tickers will be in each cell? The problem if you don't is that you need to concatenate the text of each ticker to produce a result in a single cell - this is problematic from a formula perspective. You could do this via a UDF and then have this used as a formula, but this of course still requires coding.

  9. #9
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    assuming its always going to be xxxx:xxxx or xxxx:xxx
    then will return up to 5 instances

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    No but we can give a mx value, say like 100 tickers and then run a function ?

  11. #11
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    Martin - Your solution works fine for me except that the exchange:tickers doesn't have to be limited to the number of characters you've mentioned. Thanks for all ur help guys...

  12. #12
    Valued Forum Contributor Richard Schollar's Avatar
    Join Date
    05-23-2006
    Location
    Hampshire UK
    MS-Off Ver
    Excel 2002
    Posts
    1,264
    The following UDF needs to be inserted into a standard module in the workbook. Then use in a worksheet cell like:

    =GetTickers(A1)

    Please Login or Register  to view this content.
    It's another option.

    Richard

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    Please Login or Register  to view this content.
    will do 4 of any length then the formula gets tooooooo big

  14. #14
    Registered User
    Join Date
    03-03-2006
    Posts
    27
    Thanks Richard. This is perfect

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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