Sum $ within a cell

[jjj2k]

I have two columns

Current Plan -------- Target Plan
$49 / 3GB + $10 OptK Tier 8 -------- $39 / 3GB 24m Modem
$49 / 3GB + $20 OptK Tier 7 -------- $39 / 3GB 24m Modem
$49 / 3GB + $24 OptK Tier 6 -------- $39 / 3GB 24m Modem
$49 / 3GB + $29 OptK Tier 5 -------- $39 / 3GB 24m Modem

(columns separated by -------- here but in excel belongs next to each other in Column A and B without the -------- separator )

I want to sum all the values within Current Plan,
eg. from first row $49 + $10 = $59
for 2nd row, $49 + $20 = $69

etc.

and substract total of [Target Plan] column,
so for first row, $59 - $39
2nd row, $69 - $39
and so on
is there any easy way to do it using excel/vba? I don't want to do 4000 of them by hand!

[Trebor76]

Hi jjj2k,

Assuming the data starts in Row 2 (change to suit) the follwing formula will work if the strtucture of the string in Col's A and B remains the same:

=VALUE(LEFT(A2,3))+VALUE(MID(A2,13,3))-VALUE(LEFT(B2,3))

HTH

Robert

[jjj2k]

Hi Trebor76, this works fine for about 60% of them until I noticed the following:

$59 / 1GB + $135 OptK Tier 1
$29 / 300MB + $20 OptK Tier 7
$59 / 1GB + $45 OptK Tier 4
$59 / 1GB + $67.50 OptK Tier 3
$29 / 300MB + $90 OptK Tier 2
$39 / 1GB + $90 OptK Tier 2
$40 / Blackberry 24m + $59.95 / Blackberry Casual
$40 / Blackberry 24m + $79.95 / Blackberry 24m
$44.95 / Blackberry 24m
$44.95 / CSIRO Blackberry
$49.95 / Blackberry 24m
$49.95 / Blackberry 24m + Databundle Gprs $10
$59.95 / Blackberry Casual
$59.95 / Blackberry Casual + $10 / 150MB
$59.95 / Blackberry Casual + $5 / 5MB
$59.95 / Blackberry Casual + Databundle Gprs $5
$64.95 / Blackberry 24m
$39 / 1GB + $40 / Blackberry 24m
$39 / 1GB + $59.95 / Blackberry Casual
$40 / Blackberry 24m + $29 / 1GB 24m Modem
$40 / Blackberry 24m + $39 / 1GB
$49 / 3GB + $59.95 / Blackberry Casual
$59.95 / Blackberry Casual + $29 / 300MB
$40 / Blackberry 24m + $39 / 1GB + $29 OptK Tier 5
$44.95 / CSIRO Blackberry + $10 OptK Tier 8
$49.95 / Blackberry 24m + $10 OptK Tier 8
$59.95 / Blackberry Casual + $10 OptK Tier 8
$59.95 / Blackberry Casual + $135 OptK Tier 1
$44.95 / CSIRO Blackberry + $20 OptK Tier 7
$59.95 / Blackberry Casual + $20 OptK Tier 7
$44.95 / CSIRO Blackberry + $24 OptK Tier 6
$59.95 / Blackberry Casual + $24 OptK Tier 6
$44.95 / CSIRO Blackberry + $29 OptK Tier 5
$49.95 / Blackberry 24m + $29 OptK Tier 5
$49.95 / Blackberry Casual + $29 OptK Tier 5
$59.95 / Blackberry Casual + $5 / 5MB + $29 OptK Tier 5
$59.95 / Blackberry Casual + $39 / 1GB
$49.95 / Blackberry 24m + $39 / 3GB 24m Modem
$44.95 / CSIRO Blackberry + $45 OptK Tier 4

so the lengths are changing and not static as I first assumed it to be.

[Trebor76]

so the lengths are changing and not static as I first assumed it to be

Nor are there always two amounts in the string. As such, try the Text to Columns option using 'Space' as the delimiter.