[AccessD] Parsing Variable Length Text String with VBA - Somebody didn't understand Normalization

All,

We are using Access 2007 to pull data from a purchased package SQL
Server database.  

One of the fields that we are working with is a long text string that
actually contains multiple pieces of data that we would like to work
with on an individual level.

Here is an example of how the pieces of data are found in the text
string.

State [Florida] Country [USA] Color [Green] Division [Southeast] ...
etc.

There can be from 10 to 30 individual pieces of data contained in the
text string.

Ideally each of these pieces of data would be individual fields, but we
cannot change how the data is stored in the purchased package.

We currently use Access 2007 to present this data to our users.  This
works nicely for the most part.  

Recently one of our managers has requested that we change the color to
red for all of the pieces of data that are contained in the brackets.
In other words, if we have the string   

State [Florida] Country [USA] Color [Green] Division [Southeast]

the words Florida, USA, Green, and Southeast would be shown in red on
Access reports.



What further complicates all of this is the fact that there is a
variable number of pieces of data.  In this example, there are 4 pieces
of data, but there can be up to 30 or maybe even more in the future.

We have done some experimenting with parsing the big text field and
creating small fields by looking for the brackets and the data between
the brackets - [Florida] for example .  This will probably work, but
before we go further with this approach I wanted to see if anyone else
has ever run into something like this.

Thanks for your help,

Brad  

PS.  The data cannot be stored in Rich Text Format - it currently can
only be stored as simple text. 

PSS.  I have an extra copy of a book on database normalization that I
may send to the software vendor :-)