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>
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Philosophaie

Active Member
Joined
Mar 5, 2010
Messages
254
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

Active Member
Joined
Jun 19, 2002
Messages
274
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

Well-known Member
Joined
Mar 20, 2013
Messages
883
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

They are amounts in money and people initials.
Assuming the initials are always 2 characters ..

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

nuked

Well-known Member
Joined
Mar 20, 2013
Messages
883
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
 

jubszz

New Member
Joined
Nov 21, 2012
Messages
14

ADVERTISEMENT

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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

jubszz

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

Watch MrExcel Video

Forum statistics

Threads
1,122,968
Messages
5,599,098
Members
414,287
Latest member
Rbwester

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
Top