software

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(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
Marc,

Replace the 7s and 6s with FIND(quot;,quot;,F4)-FIND(quot;=quot;,F4) 2

for each cell.

HTH,
Bernie
MS Excel MVPquot;Marcquot; gt; wrote in message
...
gt; Hello - In my example I have 3 cells that have a combination of letters and
gt; numbers. I want to extract the numbers and total them in my formula cell.
gt; The formula below works, but I'm looking for a way to use a variable to
gt; extract the lenght of the number instead of hard coding the length. 156,400
gt; and 394,450 are each 7, but 53,000 is only 6.
gt;
gt; Thanks for any help you can give.
gt;
gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt; discretionary
gt;
gt; F5 - 575 @ 686 = 394,450 (partnership)
gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt; hours x 7/hour
gt;
gt; FORMULA -
gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt;
gt;
try
=MID(E8,FIND(quot;=quot;,E8) 1,FIND(quot;(quot;,E8)-FIND(quot;=quot;,E8)-1)

--
Don Guillett
SalesAid Software

quot;Marcquot; gt; wrote in message
...
gt; Hello - In my example I have 3 cells that have a combination of letters
gt; and
gt; numbers. I want to extract the numbers and total them in my formula cell.
gt; The formula below works, but I'm looking for a way to use a variable to
gt; extract the lenght of the number instead of hard coding the length.
gt; 156,400
gt; and 394,450 are each 7, but 53,000 is only 6.
gt;
gt; Thanks for any help you can give.
gt;
gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt; discretionary
gt;
gt; F5 - 575 @ 686 = 394,450 (partnership)
gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt; hours x 7/hour
gt;
gt; FORMULA -
gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt;
gt;
If you really need to, use =MID(F4,FIND(quot;=quot;,F4) 2,FIND(quot;
quot;,F4,FIND(quot;=quot;,F4) 2)-FIND(quot;=quot;,F4)-2)
etc

Why bother, the formula works as is. But you do not need the SUM; try
=TEXT((MID(F4,FIND(quot;=quot;,F4) 2,7) (MID(F5,FIND(quot;=quot;,F 5) 2,7))) (MID(F6,FIND(quot;=quot;,F6) 2,6)),quot;0,000quot;)

Alternatively, this works for me
=--(MID(F4,FIND(quot;=quot;,F4) 2,7)) --MID(F5,FIND(quot;=quot;,F5) 2,7)--MID(F6,FIND(quot;=quot;,F6) 2,7)
then format the cell to display thousands separator
The double negations change text to number
The last one becomes quot;53000 quot; 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

quot;Marcquot; gt; wrote in message
...
gt; Hello - In my example I have 3 cells that have a combination of letters
gt; and
gt; numbers. I want to extract the numbers and total them in my formula cell.
gt; The formula below works, but I'm looking for a way to use a variable to
gt; extract the lenght of the number instead of hard coding the length.
gt; 156,400
gt; and 394,450 are each 7, but 53,000 is only 6.
gt;
gt; Thanks for any help you can give.
gt;
gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt; discretionary
gt;
gt; F5 - 575 @ 686 = 394,450 (partnership)
gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt; hours x 7/hour
gt;
gt; FORMULA -
gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt;
gt;
Don - I like your suggestion, but not every cell has a (

Marc

quot;Don Guillettquot; wrote:

gt; try
gt; =MID(E8,FIND(quot;=quot;,E8) 1,FIND(quot;(quot;,E8)-FIND(quot;=quot;,E8)-1)
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Marcquot; gt; wrote in message
gt; ...
gt; gt; Hello - In my example I have 3 cells that have a combination of letters
gt; gt; and
gt; gt; numbers. I want to extract the numbers and total them in my formula cell.
gt; gt; The formula below works, but I'm looking for a way to use a variable to
gt; gt; extract the lenght of the number instead of hard coding the length.
gt; gt; 156,400
gt; gt; and 394,450 are each 7, but 53,000 is only 6.
gt; gt;
gt; gt; Thanks for any help you can give.
gt; gt;
gt; gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt; gt; discretionary
gt; gt;
gt; gt; F5 - 575 @ 686 = 394,450 (partnership)
gt; gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt; gt; hours x 7/hour
gt; gt;
gt; gt; FORMULA -
gt; gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt; gt;
gt; gt;
gt;
gt;
gt;

try this IF there are no quot;,quot; s before your quot;=quot; and all are in ,000

=MID(E10,FIND(quot;=quot;,E10) 1,FIND(quot;,quot;,E10) 4-FIND(quot;=quot;,E10)-1)
--
Don Guillett
SalesAid Software

quot;Marcquot; gt; wrote in message
...
gt; Don - I like your suggestion, but not every cell has a (
gt;
gt; Marc
gt;
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; try
gt;gt; =MID(E8,FIND(quot;=quot;,E8) 1,FIND(quot;(quot;,E8)-FIND(quot;=quot;,E8)-1)
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Marcquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Hello - In my example I have 3 cells that have a combination of
gt;gt; gt; letters
gt;gt; gt; and
gt;gt; gt; numbers. I want to extract the numbers and total them in my formula
gt;gt; gt; cell.
gt;gt; gt; The formula below works, but I'm looking for a way to use a variable to
gt;gt; gt; extract the lenght of the number instead of hard coding the length.
gt;gt; gt; 156,400
gt;gt; gt; and 394,450 are each 7, but 53,000 is only 6.
gt;gt; gt;
gt;gt; gt; Thanks for any help you can give.
gt;gt; gt;
gt;gt; gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt;gt; gt; discretionary
gt;gt; gt;
gt;gt; gt; F5 - 575 @ 686 = 394,450 (partnership)
gt;gt; gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt;gt; gt; hours x 7/hour
gt;gt; gt;
gt;gt; gt; FORMULA -
gt;gt; gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt;gt; gt;
gt;gt; gt;
gt;gt;
gt;gt;
gt;gt;
=TEXT(SUMPRODUCT(VALUE(LEFT(RIGHT(F4:F6,LEN(F4:F6)-FIND(quot;=quot;,F4:F6)-1),FIND(quot;
quot;,RIGHT(F4:F6,LEN(F4:F6)-FIND(quot;=quot;,F4:F6)-1))))),quot;0,000quot;)

quot;Marcquot; wrote:

gt; Hello - In my example I have 3 cells that have a combination of letters and
gt; numbers. I want to extract the numbers and total them in my formula cell.
gt; The formula below works, but I'm looking for a way to use a variable to
gt; extract the lenght of the number instead of hard coding the length. 156,400
gt; and 394,450 are each 7, but 53,000 is only 6.
gt;
gt; Thanks for any help you can give.
gt;
gt; F4 - 200 @ 782 = 156,400 50 - target changes 60 - mgmt changes 90 -
gt; discretionary
gt;
gt; F5 - 575 @ 686 = 394,450 (partnership)
gt; F6 - 50 @ 1060 = 53,000 (full service) 22,000 units x $38/1000 SGI - 32
gt; hours x 7/hour
gt;
gt; FORMULA -
gt; =text(sum(mid(f4,find(quot;=quot;,f4) 2,7) (mid(f5,find(quot;= quot;,f5)=2,7)))=(mid(f6,find(quot;=quot;,f6) 2,6)),quot;0,000 quot;)
gt;
gt;