nesting multiple formulas involving cell properties

First, thanks to everyone who's helped me get this far.

I need to manipulate some table data in order to perpare it for further analysis.

I have a workbook (example attached) with an example table. The workbook has two worksheets: Finished and Orig

In the Orig, note the following:

1. Some of the cells have a mixture of text and numbers.
2. The cells with data have either green or red fonts. (for positive or negative).
3. Some cells are blank. (meaning no activity for the month) Per an earlier thread, I need blank cells to remain blank, as opposed to a "0" because a "0" corrupts my formulas.

In the Finished sheet, I need the following:

1. For blank cells to remain blank, I'm using this formula:

=IF(C13<>0,C13,"")]

2. To turn red fonts into a negative number, I first installed a.bas module from here: http://www.cpearson.com/excel/colors.aspx which determines cell properties: The "51" below indicates that the font color is green. If it is not green, then we multiply x -1 to create a negative number.

=if((=COLORINDEXOFONECELL(V15,TRUE,1))=51,+v15*1,v15*-1)

3. In the cells with a mixture of numbers and text, the text is always "est" so I created this formula to remove the "est."

=SUBSTITUTE(C13,"est","")

Now the problem!

How do I combine these formulas so that it all works? If I were to use formula 2 first, then I'll get a "0" for the solution. This screws up the ability to make distinctions between blank cells and true 0's.

If I were to use forumla 3 first, the result does not preserve the original font color. So we don't know if the number is positive or negative.

If I use formula 1 first, this doesn't help because forumua 2 will put a "0" back into the cell.

So I need a guru to help me out here. My IQ isn't high enough to combine all this into a workable solution!

Thanks

See formula 4 for solution

Originally Posted by 6StringJazzer

=IF(B8="","",SUBSTITUTE(SUBSTITUTE(B8,"*",""),"est","")*IF(COLORINDEXOFONECELL(B8,TRUE,1)=51,1,-1))

I'm sorry if this comes off being pedantic but my nature requires me to tell you that the word is "asterisk".