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 ")

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 ")

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 ")

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 ")

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 ")

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 ")

=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 ")