adding numbers ignoring txt

[jubszz]

Rite guys
I have the following numbers on my sheet. They are amounts in money and people initials. how do I just add up the numbers. if they were just numbers I wwould have just used A1:A6

825.90mh

352.70kf

650.22hj

952.25nk

354.02nm

758.25fd Any help

<colgroup><col style="mso-width-source:userset;mso-width-alt:5449;width:112pt" width="149"> </colgroup><tbody>
</tbody>

 

[Philosophaie]

How do I just add up the numbers in A1:A6 :

825.90mh

352.70kf

650.22hj

952.25nk

354.02nm

758.25fd Any help

<tbody>
</tbody>

n(1)=Left(Range("A1"),len(Range("A1"))-2)
n(2)=Left(Range("A2"),len(Range("A2"))-2)
...
n(6)=Left(Range("A6"),len(Range("A6"))-2)

num=n(1)+n(2)+n(3)+...+n(6)

 

[miss_ell]

Hello,

You can use this formula in column B and copy down, then sum column B:
=LEFT(A2,LEN(A2)-2)

Or sum column A with this formula (which would be quite awkward with a lot of data):
=LEFT(A1,LEN(A1)-2)+LEFT(A2,LEN(A2)-2)+LEFT(A3,LEN(A3)-2)+LEFT(A4,LEN(A4)-2)+LEFT(A5,LEN(A5)-2)+LEFT(A6,LEN(A6)-2)

I prefer the first option myself.

 

[nuked]

You could write your own worksheet function:-

(1) Alt+F11
(2) Insert => Module
(3) Paste the following code

Option Explicit
Function SumNumbers(ByRef Target As Range) As Double
Dim i As Integer
Dim strTmp As String
Dim dTmp As Double
Dim rng As Range

For Each rng In Target.Cells

    For i = 1 To Len(rng)
    
        If IsNumeric(Mid(rng, i, 1)) Then strTmp = strTmp & Mid(rng, i, 1)
            
    Next i

    dTmp = dTmp + CDbl(strTmp)
    strTmp = ""

Next rng

SumNumbers = dTmp

End Function

You can then use it on your worksheet as you would any other function:

=SumNumbers(A1:A100)

..for example

 

[Peter_SSs]

They are amounts in money and people initials.

Assuming the initials are always 2 characters ..

Excel Workbook
	B
1
2	825.90mh
3	352.70kf
4	650.22hj
5	952.25nk
6	354.02nm
7	758.25fd
8
9	3893.34
SUM

 

[nuked]

Or this one will handle fractions too:

Option Explicit

Function SumNumbers(ByRef Target As Range) As Double
Dim i As Integer
Dim strTmp As String
Dim dTmp As Double
Dim rng As Range

For Each rng In Target.Cells

    For i = 1 To Len(rng)
    
        If IsNumeric(Mid(rng, i, 1)) Or Asc(Mid(rng, i, 1)) = 46 Then strTmp = strTmp & Mid(rng, i, 1)
            
    Next i

    dTmp = dTmp + CDbl(strTmp)
    strTmp = ""

Next rng

SumNumbers = dTmp

End Function

 

[jubszz]

Assuming the initials are always 2 characters ..

SUM

*	B
1	*
2	825.90mh
3	352.70kf
4	650.22hj
5	952.25nk
6	354.02nm
7	758.25fd
8	*
9	3893.34

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:77px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B9 =SUMPRODUCT(--(LEFT(B2:B7,LEN(B2:B7)-2))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

this just tells me #VALUE!

 

[miss_ell]

Assuming the initials are always 2 characters ..
(snip)

Peter,

That's the best solution! I forgot about SUMPRODUCT, then again I don't use it that much...

 

[Peter_SSs]

this just tells me #VALUE!

Does it not work for the sample data provided?
Did you take note of my assumption?
Does it mean that your original sample wasn't representative?

Assuming the initials are always 2 characters ..

Or this one will handle fractions too:

Here's a simpler UDF that should do that too. No need to loop through each character in the string. This should handle negative numbers too and it doesn't error if there are any blank cells in the range.

Function SumExcl(r As Range) As Double
  Dim Tot As Double
  Dim c As Range
  
  For Each c In r
    Tot = Tot + Val(c.Value)
  Next c
  SumExcl = Tot
End Function

 

[jubszz]

Peter,

That's the best solution! I forgot about SUMPRODUCT, then again I don't use it that much...

Peter
that works fine
i wasnt looking at the cell ranges i was using, after playing around with it. It now works fine

thank you