Replace letters with Plus Sign

[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?

Thanks,

Tonso

 

[Rick Rothstein]

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)

 

[Tonso]

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)

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

 

[Rick Rothstein]

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)

 

[Ron Rosenfeld]

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), <alt-F11> 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
================================

 

[Ron Rosenfeld]

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), <alt-F11> 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.