Replace letters with Plus Sign

  • Thread starter Thread starter Tonso
  • Start date Start date
T

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
 
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?
Click to expand...

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)
 
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?
Click to expand...

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)
Click to expand...

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.
Click to expand...

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)
 
Back
Top