# Thread: extracting number from text string and doing calculations Thanks: 0 Likes: 0

1. ## extracting number from text string and doing calculations

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.

 Condition \$280 for days 1 through 6, \$0 afterwards \$240 for days 1 through 3, \$0 afterwards \$290 for days 1 through 4, \$10 afterwards \$280 for days 1 through 5, \$10 afterwards

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

2. ## Re: extracting number from text string and doing calculations

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 ??

3. ## Re: extracting number from text string and doing calculations

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.

4. ## Re: extracting number from text string and doing calculations

Something like this:

AB
1\$280 for days 1 through 6, \$0 afterwards1120
2\$240 for days 1 through 3, \$0 afterwards960
3\$290 for days 1 through 4, \$10 afterwards1160
4\$280 for days 1 through 5, \$10 afterwards1120

Sheet8

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

5. ## Re: extracting number from text string and doing calculations

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.

6. ## Re: extracting number from text string and doing calculations

Or,

AB
1\$280 for days 1 through 6, \$0 afterwards1120
2\$240 for days 1 through 3, \$0 afterwards960
3\$290 for days 1 through 4, \$10 afterwards1160
4\$280 for days 1 through 5, \$10 afterwards1120

Sheet8

Worksheet Formulas
CellFormula
B1=MID(A1,2,FIND(" ",A1)-2)*IF(MID(A1,FIND(",",A1)-1,1)>=4,4,MID(A1,FIND(",",A1)-1,1))

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•