" 2,508.00*"

This is why it doesn't work! Your parsing process needs to strip out
the leading space and trailing asterisk.

Here's a macro that parses the dollar amount from the original
string...

Sub ParseCurrency()
Dim vData, vTmp, n&, lLastCell&
lLastCell = Cells(Rows.Count, Selection.Column).End(xlUp).Row

vData = Selection.Resize(lLastCell, 1)
For n = LBound(vData) To UBound(vData)
vTmp = Split(vData(n, 1), ": ")
vData(n, 1) = Replace(vTmp(1), "*", "")
Next 'n
With Selection.Resize(lLastCell, 1): .Value = vData: .Style =
"Currency": End With
End Sub

...where the original string is like "Total Bail Amount: 2,508.00*" for
each entry. *You must select the first cell of data containing dollar
amounts before running the macro*

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion