+ Reply to Thread
Results 1 to 4 of 4

Extract Ticker Symbols (all capitals) from Text String

  1. #1
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Extract Ticker Symbols (all capitals) from Text String

    When I copy data from a pdf containing 4 columns of ticker symbols and company names into excel, it pastes all the columns into one cell. For example:

    AAPL Apple, Inc. NKE Nike, Inc.

    This should be 4 columns, with one for each ticker symbol and one for each company name. I'm wondering if there's a way to extract only the ticker symbols. Unfortunately the company names aren't consistent in the sense that they all end in "Inc." or anything like that. So I can't run an extract based on searching for those words.

    I can extract the first ticker symbol fairly easily with the array formula below:

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


    But I can't adjust it to populate NKE. Any suggestions?

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Ticker Symbols (all capitals) from Text String

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Valued Forum Contributor
    Join Date
    07-07-2014
    Location
    Washington DC
    MS-Off Ver
    2007
    Posts
    1,047

    Re: Extract Ticker Symbols (all capitals) from Text String

    Sample workbook with desired outcome attached. As you can see, I can successfully extract the first ticker symbol from the text string, I just can't extract the second one.
    Attached Files Attached Files

  4. #4
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Extract Ticker Symbols (all capitals) from Text String

    Can't guaranty that it will work on all strings as the data is very inconsistent. But for your example try this formula

    In B1 enter formula and pull it to the right and then down

    =TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(","&TRIM(SUBSTITUTE(SUBSTITUTE($A1,"Incorporated"," "),"Inc."," ")),",",REPT(" ",255)),255*COLUMNS($A:A),255))," ",REPT(" ",50)),50))

    Data Range
    A
    B
    C
    1
    AAPL Apple, Inc. NKE Nike, Inc.
    AAPL
    NKE
    2
    CNK Cinemark Holdings, Inc. GIS General Mills, Inc.
    CNK
    GIS
    3
    DBD Diebold, Incorporated HAS Hasbro, Inc.
    DBD
    HAS

+ 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. [SOLVED] Counting symbols from a text string
    By JackBauer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-26-2014, 03:13 AM
  2. Macro to Run through list of stock ticker symbols
    By johnk13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2014, 08:48 PM
  3. Replace old ticker symbols
    By worswick25 in forum Excel - New Users/Basics
    Replies: 8
    Last Post: 09-14-2011, 01:29 PM
  4. pull out text between 2 symbols in a string within a cell
    By jasonanthony in forum Excel General
    Replies: 3
    Last Post: 02-02-2009, 06:18 PM
  5. Pulling certain info from Yahoo Finance without ticker symbols
    By wolfy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-08-2009, 11:37 AM
  6. the # of data records that correspond to the ticker symbols
    By excelbear in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 12-16-2007, 06:36 PM
  7. Recognize more stock ticker symbols?
    By Bob in forum Excel General
    Replies: 2
    Last Post: 01-26-2005, 03:06 AM

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