#### jubszz

##### New Member
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)

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.

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

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

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``````

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>

 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!

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...

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``````

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

Replies
4
Views
788
Replies
10
Views
2K
Replies
51
Views
5K
Replies
6
Views
1K
Replies
10
Views
959

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.

### Which adblocker are you using?

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

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