Find and Length

[Guest]

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

 

[Bernie Deitrick]

Marc,

Replace the 7s and 6s with FIND(",",F4)-FIND("=",F4)+2

for each cell.

HTH,
Bernie
MS Excel MVP

 

[Don Guillett]

try
=MID(E8,FIND("=",E8)+1,FIND("(",E8)-FIND("=",E8)-1)

 

[Bernard Liengme]

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("=",F5)+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)

 

[Guest]

Don - I like your suggestion, but not every cell has a (

Marc

 

[Don Guillett]

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)

 

[Guest]

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