Help needed (Automatic script function)

[Soul]

Hi there,

I have a slight problem and I hope that the people at this forum can
give me a hand.

I have a database which has over 200 pages and I would need a script
which could do the following to ease my
work;

http://img233.imageshack.us/img233/4388/excel6wg.jpg

I need the script to add the value in the E column (the way I show
within the brackets). The script should ignore letters, commas points
etc, and just add up the numbers to a 2 digit value, placeing the
answer in the H column.

I would greatly appreciate if someone could lend me a helping hand with
this, since I have to add the up manually until now... Thats what I get
for not knowing excel

thnx for your time,

Soul.

 

[Ron Coderre]

For a value in A1....
B1
=SUMPRODUCT(IF(--ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))

Note: commit that array formula by holding down [Ctrl]+[Shift] an
press [Enter]

Does that help?

Regards,
Ro

 

[Ron Coderre]

For a value in A1....
B1:
=SUMPRODUCT(IF(--ISNUMBER(--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)),--MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)))

Note: commit that array formula by holding down [Ctrl]+[Shift] and
press [Enter]

Does that help?

Regards,
Ron

 

[Guest]

Soul,

Try the following script -- this one is written for row 1 -- you'd have to
loop to get the rest of the rows.

Sub temp()
Dim i As Integer
Dim j As Integer
For i = 1 To Len(Cells(1, 6))
If IsNumeric(Cells(1, 6)) Then
x = x + Val(Mid(Cells(1, 6), i, 1))
End If
Next i
Cells(1, 8) = x
End Sub

Art

 

[Guest]

Ron,

Wow! -- I thought this needed to be scripted.

Art

 

[Soul]

Yo ppl,

First of all, thx for such a fast reply. I tried the formula, although
since I have the Spanish version of Excel, I doubt the formulas could
work since it seems, that even the commands have been translated. I
have to fish out the english version and try it asap. When I did add
the fomula, it asked me for #NAME? and didnt seen to work. Im sorry
about this, but Im a total noob in Excel but Im forced to use it ......
Ill give it a try again asap, and Ill msg back for help if it doesnt
work. Thx Art / Ron.

Soul.