Replace letters with Plus Sign

I have a workbook that has cells containing numbers and letters, as shown in the example below:

V5H0R15J2W127R9

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?

Thanks,

Tonso

I have a workbook that has cells containing numbers and letters, as shown in the example below:

V5H0R15J2W127R9

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)

On May 18, 1:14 pm, "Rick Rothstein"

letters, as shown in the example below:

V5H0R15J2W127R9

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)

Rick,

I did as you said, but i get "Compile Error: Syntax Error, and from Dim X.... down through Next is in red. I am certain I copied and pasted as directed, creating module as you said.

Billy

I did as you said, but i get "Compile Error: Syntax Error, and from Dim X.... down through Next is in red. I am certain I copied and pasted as directed, creating module as you said.

I'm not sure what to tell you... there is no syntax error in the code I posted... it was tested before I posted it, but just to be sure, I retested it again right now... the code works fine. Try repeating the process again. If you still get the error, you can send your workbook to me and I will try to see why you are having a problem (send the version with the copied code that is showing the syntax error). My email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbols they spell out).

Rick Rothstein (MVP - Excel)

I have a workbook that has cells containing numbers and letters, as shown in the example below:

V5H0R15J2W127R9

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?

Thanks,

Tonso

Can be done with a User Defined Function:

To enter this User Defined Function (UDF), opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SumNums(A1)

in some cell.

If the string is not well formed (e.g. ends with a letter or has more than a single letter at any location), this will return a #VALUE! error, but, if you are specific, error testing can be done within the UDF.

============================= Option Explicit Function SumNums(s As String) Dim re As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "[A-Za-z]" re.Global = True SumNums = Evaluate(re.Replace(s, "+")) End Function ================================

I have a workbook that has cells containing numbers and letters, as shown in the example below:

V5H0R15J2W127R9

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?

Thanks,

Tonso

Can be done with a User Defined Function:

To enter this User Defined Function (UDF), opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.

To use this User Defined Function (UDF), enter a formula like

=SumNums(A1)

in some cell.

If the string is not well formed (e.g. ends with a letter or has more than a single letter at any location), this will return a #VALUE! error, but, if you are specific, error testing can be done within the UDF.

============================= Option Explicit Function SumNums(s As String) Dim re As Object Set re = CreateObject("vbscript.regexp") re.Pattern = "[A-Za-z]" re.Global = True SumNums = Evaluate(re.Replace(s, "+")) End Function ================================

Actually, it will ignore non-singular letters, but it will give an error if a letter occurs at the end of the string.

Testing that the string follows your rules can still be incorporated, if you wish.