Sum a $ amount in part of a field

[Guest]

I have payment lines that export to Excel as one field/cell - at times, there
may be as many as 45 or 50 payments that are strung together, separated only
by the "|" (bar) key.

I.e.
02-19-03 01-31-03 220818 $125.00 151577 VOL.5 NO.2 | 08-13-03
08-04-03 243736 $125.00 158021 VOL.5 NO.1 | 10-14-03 09-13-03
S-59651 -$10.50 159582 RATE ADJ

Is there a formula I can use to pull out just the $ amounts from this cell,
and then sum it up (i.e. $239.50)?

I could split the field using the "|" as a delimiter, but then I might have
40 or 50 columns and I still need to figure out how I can extract all the $
amounts, then have it sum correctly.

 

[Biff]

Hi!

Good luck with this!

Here's something you can try provided text to columns will handle an entry
so long:

may be as many as 45 or 50 payments that are strung together, separated
only
by the "|" (bar) key.

I used T to C on the "short" piece of sample data you posted and got this
output:

A1 = 02-19-03 01-31-03 220818 $125.00 151577 VOL.5 NO.2
B1 = 08-13-03 08-04-03 243736 $125.00 158021 VOL.5 NO.1
C1 = 10-14-03 09-13-03 S-59651 -$10.50 159582 RATE ADJ

To extract the dollar amounts enter this formula in say A3 and copy across
to C3:

=--TRIM(MID(A1,FIND("$",A1)-1,FIND(" ",A1,FIND("$",A1))-FIND("$",A1)+1))

Results were:

A3 = 125
B3 = 125
C3 = -10.5

Now, the final result:

=SUM(A3:C3)

Without the "--" in the formula, the results would be text like $125.00,
$125.00, and -$10.50.

The "--" "converts" the text numbers to numeric numbers and in the process,
you lose all the formatting: eg: $ sign and insignificant zeros.

Good luck!

Biff