S

#### Simon Woods

containing a text string and a number, for example:

If I had the cell values:

8ABC, 10ABC, 2XYZ

And I wanted to calculate the sum of the numeric portions of cells

containing the strong ABC, the result would be 18.

I know the text string will always be 3 characters long, and so I've tried

this:

=SUM(IF(RIGHT($L49:$CY49,3)="ABC",LEFT($L49:$CY49,LEN($L49:$CY49)-3)))

Which, I think, should check all cells in the range L49 to CY49 to see if

the rightmost 3 characters are"ABC" and, where that's true, assign the

numeric value of that cell by trimming the text string (so 10ABC becomes 10

and so on) and then adding those values together.

But, the formula dies on the first step, returning #value! for the function

RIGHT($L49:CY49). If I click the Insert Function button, the function appears

to evaluate correctly in the "Function arguments" dialogue box.

I've seen a similar function work in another spreadsheet, but the function

is surrounded by curly braces in the function bar. I don't know if that makes

a difference.

Any ideas what I'm doing wrong?