I have a workbook that has cells containing numbers and

letters, as shown in the example below:

The 1st character is always a letter, the last is always a

number. The letters always appear singularly, but the

numbers can be 1, 2, or 3 digits. The can be anywhere

from 5 letters in a cell to 12, each letter always followed

by fomr 1 to 3 numbers. What i would like to do is to, in

another cell, add the numbers, so the formula, based on

the example above, would be: 5+0+15+2+127+9, and the

result would be 158. Is there a formula that would

accomplish this?

I think a formula to do this, if it is even possible, would be quite

complex... how about a UDF (user defined function) instead?

Function SumNumbers(ByVal S As String) As Double

Dim X As Long

For X = 1 To Len(S)

If Mid(S, X, 1) Like "[!0-9]" Then Mid(S, X, 1) = " "

Next

SumNumbers = Evaluate(Replace(WorksheetFunction.Trim(S), " ", "+"))

End Function

To install this UDF, press ALT+F11 to go into the VB editor, click

Insert/Module once there and copy/paste the above code into the code window

that opened up. That's it. You can now use SumNumbers just like a built-in

Excel function. Go back to your worksheet and, assuming your first

number/letter combination text is in A1, put this formula in a different

cell...

=SumNumbers(A1)

This formula can be copied down or across as needed.

Rick Rothstein (MVP - Excel)