extracting number from text string and doing calculations

[Vicasso]

I have to extract the left most amount and the number of days from the below attached text string and perform mathematical operations depending on the number of days.

[TABLE="width: 322"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Condition [/TD]
[/TR]
[TR]
[TD]$280 for days 1 through 6, $0 afterwards[/TD]
[/TR]
[TR]
[TD]$240 for days 1 through 3, $0 afterwards[/TD]
[/TR]
[TR]
[TD]$290 for days 1 through 4, $10 afterwards[/TD]
[/TR]
[TR]
[TD]$280 for days 1 through 5, $10 afterwards[/TD]
[/TR]
</tbody>[/TABLE]

Final sum for all the below rows to be calculated according to logic.
Amount*4 ( If number of days if >=4)
Amount*number of days( If number of days is less than 4)

Example
1) The first sum should be 280*4
2) second sum should be 240*3

 

[Michael M]

looks like an assignment to me !
what have you tried so far AND how did you arrive at The first sum should be 280*4 ??

 

[Vicasso]

I was able to extract the leftmost amount value using the LEFT Function.
the problem comes when i have to extract the day value, i.e 6 in the first case, 3 in the 2nd case.

how did you arrive at The first sum should be 280*4 ??
because as the logic states we have to multiply with 4 if the number of days is >=4, in the first case it's 6 so we have to multiply the amount with 4.

 

[Nishant94]

Something like this:

Book1
	A	B
1	$280 for days 1 through 6, $0 afterwards	1120
2	$240 for days 1 through 3, $0 afterwards	960
3	$290 for days 1 through 4, $10 afterwards	1160
4	$280 for days 1 through 5, $10 afterwards	1120
Sheet8

Cell Formulas
Range	Formula
B1	=(MID(A1,2,FIND(" ",A1)-2))*IF(MID(A1,SEARCH("through",A1)+8,1)>=4,4,MID(A1,SEARCH("through",A1)+8,1))

 

[kweaver]

I agree w/Michael: it sounds like a class assignment. Are the four sums: 1120, 720, 1160, and 1120? Try it by hand and see what you should get.

I don't agree w/your second number and suspect it's because you search returned a character 3 versus a number 3.

BTW, you could also look for the comma.

 

[Nishant94]

Or,

Book1
	A	B
1	$280 for days 1 through 6, $0 afterwards	1120
2	$240 for days 1 through 3, $0 afterwards	960
3	$290 for days 1 through 4, $10 afterwards	1160
4	$280 for days 1 through 5, $10 afterwards	1120
Sheet8

Cell Formulas
Range	Formula
B1	=MID(A1,2,FIND(" ",A1)-2)*IF(MID(A1,FIND(",",A1)-1,1)>=4,4,MID(A1,FIND(",",A1)-1,1))