adding numbers ignoring txt

jubszz

New Member
Joined
Nov 21, 2012
Messages
14
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>
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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)
 
Upvote 0
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.
 
Upvote 0
You could write your own worksheet function:-

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

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
 
Upvote 0
Or this one will handle fractions too:

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)) 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
 
Upvote 0
Assuming the initials are always 2 characters ..

SUM

*B
1*
2825.90mh
3352.70kf
4650.22hj
5952.25nk
6354.02nm
7758.25fd
8*
93893.34

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

Spreadsheet Formulas
CellFormula
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!
 
Upvote 0
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.
Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,214,622
Messages
6,120,585
Members
448,972
Latest member
Shantanu2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top