0

I am trying to write a VB script in Excel to parse some data in an Excel cell. To parse the data correctly, I need to utilize the formatting in the cell. For example, the text to be parsed below should be parsed as follows: a. MINESHAFT B. DARNLEY BAY. The only way to tell this is because MINESHAFT is displayed in a smaller font.

Is there anyway I could right a VB script that could parse the cell text based upon the hidden formatting codes in the cell.

Text to be parsed: MINESHAFT DARNLEY BAY

I'm currently trying to accomplish this in Mac 2011 Office Excel, but I could also do in on a PC Excel 2010 if it makes a difference.

Thanks for you help.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
Mutuelinvestor
  • 3,384
  • 10
  • 44
  • 75

1 Answers1

0

I'm sure you probably can find out the font of a section, but I think you should analyse your data to try and find another way of pulling out the information you want.

  1. Why is the first word a different font?
  2. Are there a finite number of MINESHAFT words?
  3. Could you find the MINESHAFT, ETC word at the begining and put that in a, and then put the rest of the string into b?

If you post a greater amount of data you are trying to parse we might be able to help.

Toby Allen
  • 10,997
  • 11
  • 73
  • 124
  • Toby - thanks for the reply. The first word is different because Mineshaft is the sire of Darnley Bay. The problem I face is that it could be Mineshaft Bay Cigar where the sire is Mineshaft is the sire. The different size font is the information that allows one to determine the sire vs the offspring. Failing a compete lookup list of Sires I can't think of a way of making the differentiation, but I would be very appreciative of any ideas you have. Thanks again. – Mutuelinvestor Jun 28 '14 at 10:05
  • Does the word bay have meaning? Does its position have meaning? – Toby Allen Jun 28 '14 at 10:45
  • No, just part of the name. – Mutuelinvestor Jun 28 '14 at 11:23
  • Toby - I think I have figured it out. I can use the Characters(i,1).font.size to determine the size of characters in a string of text. – Mutuelinvestor Jun 28 '14 at 12:22