Tags: astring, cboe, cell, characters, contains, copying, date, excel, expiry, extract, identifying, microsoft, msdn, price, software, strike, text, website

How do I extract part of a text in a cell?

On Microsoft » Microsoft Excel

1,520 words with 2 Comments; publish: Sun, 01 Jun 2008 14:25:00 GMT; (306125.00, « »)

I am copying option information from the CBOE website, which contains a

string of characters identifying the option expiry date, strike price, and

ticker symbol (e.g. 07 Jul 55.00 (BZHSK-E). I would like to pull out the 55.00

All Comments

Leave a comment...

  • 2 Comments
    • There are a couple of ways and the correct answer depends on the layout of

      your data strings. In the one you supplied the 5 is the 8th character and you

      want 5 characters so this works:-

      =MID(A1,8,5)

      It may be that you r strings aren't that regular and there are ways around

      that using 'Find' so post again with more string example is this doesn't work.

      Mike

      "CHI" wrote:

      > I am copying option information from the CBOE website, which contains a

      > string of characters identifying the option expiry date, strike price, and

      > ticker symbol (e.g. 07 Jul 55.00 (BZHSK-E). I would like to pull out the 55.00

      #1; Sun, 01 Jun 2008 14:26:00 GMT
    • >I am copying option information from the CBOE website, which contains a

      > string of characters identifying the option expiry date, strike price, and

      > ticker symbol (e.g. 07 Jul 55.00 (BZHSK-E). I would like to pull out the

      > 55.00

      If each string value is always laid out as you showed it...

      year<space>month<space>price<space>symbol

      then you can use Split to get at the piece you want. For example...

      InfoLine = "07 Jul 55.00 (BZHSK-E)"

      Price = Split(InfoLine)(2)

      Rick

      #2; Sun, 01 Jun 2008 14:27:00 GMT