|
Find and Length
Hello - In my example I have 3 cells that have a combination of letters and
numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
Find and Length
Marc,
Replace the 7s and 6s with FIND(",",F4)-FIND("=",F4)+2 for each cell. HTH, Bernie MS Excel MVP "Marc" wrote in message ... Hello - In my example I have 3 cells that have a combination of letters and numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
Find and Length
If you really need to, use =MID(F4,FIND("=",F4)+2,FIND("
",F4,FIND("=",F4)+2)-FIND("=",F4)-2) etc Why bother, the formula works as is. But you do not need the SUM; try =TEXT((MID(F4,FIND("=",F4)+2,7)+(MID(F5,FIND("=",F 5)+2,7)))+(MID(F6,FIND("=",F6)+2,6)),"0,000") Alternatively, this works for me =--(MID(F4,FIND("=",F4)+2,7))+--MID(F5,FIND("=",F5)+2,7)--MID(F6,FIND("=",F6)+2,7) then format the cell to display thousands separator The double negations change text to number The last one becomes "53000 " but when turned into a number the trailing space disappears It would not work if the value had less than 6 or more than 7 characters (digits plus comma) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Marc" wrote in message ... Hello - In my example I have 3 cells that have a combination of letters and numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
Find and Length
Don - I like your suggestion, but not every cell has a (
Marc "Don Guillett" wrote: try =MID(E8,FIND("=",E8)+1,FIND("(",E8)-FIND("=",E8)-1) -- Don Guillett SalesAid Software "Marc" wrote in message ... Hello - In my example I have 3 cells that have a combination of letters and numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
Find and Length
try this IF there are no "," s before your "=" and all are in ,000
=MID(E10,FIND("=",E10)+1,FIND(",",E10)+4-FIND("=",E10)-1) -- Don Guillett SalesAid Software "Marc" wrote in message ... Don - I like your suggestion, but not every cell has a ( Marc "Don Guillett" wrote: try =MID(E8,FIND("=",E8)+1,FIND("(",E8)-FIND("=",E8)-1) -- Don Guillett SalesAid Software "Marc" wrote in message ... Hello - In my example I have 3 cells that have a combination of letters and numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
Find and Length
=TEXT(SUMPRODUCT(VALUE(LEFT(RIGHT(F4:F6,LEN(F4:F6)-FIND("=",F4:F6)-1),FIND("
",RIGHT(F4:F6,LEN(F4:F6)-FIND("=",F4:F6)-1))))),"0,000") "Marc" wrote: Hello - In my example I have 3 cells that have a combination of letters and numbers. I want to extract the numbers and total them in my formula cell. The formula below works, but I'm looking for a way to use a variable to extract the lenght of the number instead of hard coding the length. 156,400 and 394,450 are each 7, but 53,000 is only 6. Thanks for any help you can give. F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 - discretionary F5 - 575 @ 686 = 394,450 (partnership) F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32 hours x 7/hour FORMULA - =text(sum(mid(f4,find("=",f4)+2,7)+(mid(f5,find("= ",f5)=2,7)))=(mid(f6,find("=",f6)+2,6)),"0,000 ") |
| All times are GMT +1. The time now is 05:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com