I have a text field that may or may not contain dollar amounts embedded in the text. For example, a text field might read:

"Expenses related to our trip included $12.50 for lunch and 6.22 for supplies. At 6PM, we had a dinner that cost 21.85. Our only other expense was $6 for cab fare."

What I want to do is parse the field and extract all amounts into a companion array within that same record. The parse rules would be:

If it starts with a dollar sign, then it's definitely an amount (with or without a decimal point)

If it starts with a number only, then it must have a decimal point

I've searched these forums and read about parsing, but nothing seems close enough to really give me a place to start. My questions at this point are:

How should I construct a parse for multiple occurrences within the same text field?

Is it practical to have a calculated, unstored copy of the text field that colorizes each field that was interpreted as an amount? (to display for user confirmation purposes)

I will greatly appreciate any suggestions so I don't start on a wrong path with this. Thanks.

Would something like the attached work for you?

Parse.zip

Hi

this solves the problem w/o any script

Parse2.zip

Revision one...

Parse2.zip

Thanks for these responses.

sbg2: The scripted approach has some interesting aspects (like passing the Substitute() as a script parameter -- and the accumulating results table). Thanks.

Raybaudi: thanks for providing such an elegant solution. I've added the calc and repeating fields to the test layout to maks sure I followed the calculations -- and as a basis for designing the colorizing step. The nested substitute/ trimall/ substitute is excellent -- and not something I would have thought to try. And special thanks for including the summation of the values (I guess you could see ahead where I was going with this).