extracting numbers and adding

B

Billy

Using XL2002 for Windows, I want to extract and add numbers from cells.
for example, let the following 3 lines each represent 1 cell.

D2K13 V0F4
Y2
H4T10

How can I extract the numbers then add them. For example...

D2K13 V0F4 = 2+13+0+4 = 19
Y2 = 2
H10T4 = 10+4 = 14

Is this possible?

thanks,
Tonso
 
P

Pete_UK

You could set up a User Defined Function to replace all characters with
a + in the string, and then pass this to the Evaluate function. If you
are likely to have two or more letters next to each other then the UDF
would have to detect for this.

I'm about to go out now - I'm sure others will be able to expand on
this.

Hope this helps.

Pete
 
D

Don Guillett

try this. Change the range to suit>place in a REGULAR module>
then just use as a regular function =gn("orange") or =gn("apple") NOT appleS

Function gn(y)
application.volatile 'may not be necessary
Dim mn As Long
For Each c In Range("c2:c22")
If InStr(c, y) > 0 Then
mn = mn + Val(Left(c, InStr(c, " ")))
End If
Next
gn = mn
End Function
 
R

Ron Rosenfeld

Using XL2002 for Windows, I want to extract and add numbers from cells.
for example, let the following 3 lines each represent 1 cell.

D2K13 V0F4
Y2
H4T10

How can I extract the numbers then add them. For example...

D2K13 V0F4 = 2+13+0+4 = 19
Y2 = 2
H10T4 = 10+4 = 14

Is this possible?

thanks,
Tonso
Click to expand...


One method would be to download and install Longre's free morefunc.xll add-in
from http://xcell05.free.fr

Then use this **array** formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",ROW(
INDIRECT("1:"&REGEX.COUNT(A1,"\d+")))),"+"))

To enter an **array** formula, after copying or typing the formula into the
formula bar, hold down <ctrl><shift> while hitting <enter>. Excel will place
braces {...} around the formula.


--ron
 
D

Dana DeLouis

Here's a Regular Expression example on some test data in A1:A3. Output is
to B1:B3.
Adjust as necessary.

Sub Demo()
Dim RE As Object
Dim Cell As Range
Dim S As String
Const Sp As String = " "
Const P As String = "+"

Set RE = CreateObject("VBScript.RegExp")
RE.Global = True
RE.Pattern = "\D+"

'// Test Data
[A1] = "D2K13 V0F4"
[A2] = " Y2"
[A3] = "H10T4 "

For Each Cell In [A1:A3].Cells
If RE.test(Cell) Then
Cell(1, 2) = Evaluate(Replace(Trim(RE.Replace(Cell, Sp)), Sp, P))
End If
Next Cell
End Sub

Hopefully this will work for your data.
 
K

kounoike

Does this ,though very elementary way of coding, work in your case?

D2K13 V0F4 in A1, and put a formula =myadd(A1) in B1 , then B1 will be 19.
myadd is like this

Function myadd(ByVal s As String) As Variant
Dim t As String, v As String
If s = "" Then myadd = "": Exit Function
Do While (s <> "")
t = Mid(s, 1, 1)
If IsNumeric(t) Then
v = v & t
Else
myadd = myadd + Val(v)
v = ""
End If
s = Mid(s, 2)
Loop
myadd = myadd + Val(v)
End Function

keizi
 
L

Lori

=SUM(IF(MMULT(--ISERR(-MID(SUBSTITUTE(A1,"+","|")&"|",(ROW(1:990)-1)/10+
{0,1,1},MOD(ROW(1:990)-1,10)+{2,1,2})),{1;2;4})=5,--MID(A1,(ROW(1:990)-1)/10+
1,MOD(ROW(1:990)-1,10)+1)))

This was published in a prior post - it works on strings up to 99
characters and numbers up to 10 digits by extracting numeric portions
and summing them up.
 
L

Lori

Meant to add needs to be array entered (Ctrl+shift+enter) and when you
paste replace 1-0 with 10.
 
D

Don

Billy,

Function sumnums(mycell)
c = Len(mycell)
For i = 1 To c
totals = totals + Val(Mid(mycell, i, 1))
Next
sumnums = totals
End Function

Don
 
P

Pete_UK

Hi Don,

I've not tried it, but I don't think it would work on this:

D2K13 V0F4

Instead of recognising the 13, your UDF would take the 1 and then the 3
and add them.

Pete
 
R

Ron Rosenfeld

Billy,

Function sumnums(mycell)
c = Len(mycell)
For i = 1 To c
totals = totals + Val(Mid(mycell, i, 1))
Next
sumnums = totals
End Function

Don
Click to expand...

Your function sums the individual digits. That is NOT what the OP wanted.
--ron
 
Top