Adding multiple numbers contained within a single cell

[babycody]

Is it possible to have multiple numbers i.e. 2,43,8 in a single cell, and show the sum of these numbers in another cell? Also is it possible to add characters IIII together to get 4? Thanks ahead for any help on this matter.

 

[Scott Huish]

Also is it possible to add characters IIII together to get 4?

I found this function (slightly modified) from vb-helper.com

Private Function ARABIC(ByVal roman As String) As Variant
Dim i As Integer
Dim ch As String
Dim result As Long
Dim new_value As Long
Dim old_value As Long

roman = UCase$(roman)
old_value = 1000

For i = 1 To Len(roman)
' See what the next character is worth.
ch = Mid$(roman, i, 1)
Select Case ch
Case "I"
new_value = 1
Case "V"
new_value = 5
Case "X"
new_value = 10
Case "L"
new_value = 50
Case "C"
new_value = 100
Case "D"
new_value = 500
Case "M"
new_value = 1000
End Select

' See if this character is bigger
' than the previous one.
If new_value > old_value Then
' The new value > the previous one.
' Add this value to the result
' and subtract the previous one twice.
result = result + new_value - 2 * old_value
Else
' The new value<= the previous one.
' Add it to the result.
result = result + new_value
End If

old_value = new_value
Next i

ARABIC = result
End Function

Book1
	A	B	C	D
1	IIII	4
Sheet1

 

[ChadW]

Along these lines...

How would you approach the same situation, but without any comma's.

I.E. Cell C4 contains the following: 59214

How would you SUM those numbers (all numbers should be assumed to be single digits, 1-9, no zeros)

So, 5+9+2+1+4, or whatever numbers happen to be within that single cell

 

[Scott Huish]

Perhaps:

Public Function sumcell(s As String)
Dim a As Long, x As Long, y As String
For x = 1 To Len(s)
    y = Mid(s, x, 1)
    If IsNumeric(y) Then a = a + y
Next x
sumcell = a
End Function

Book1
	A	B	C	D
1	59214	21
Sheet1

 

[ChadW]

Perfect! Thanks!

 

[assman]

What a great and useful function, thanks a lot

 

[nickalfano43]

I am trying to create a function to add seperate numbers within a single cell. Here is what Im looking for along with a brief explaination.

1265/1028

I own a debt collection agency and my agents are collecting a down payment and setting up installments. I am trying to track there gross(1265) as well as there future EFT(1028) on a daily, weekly, monthly basis. So i am attempting to add several cells like a mon-fri... in which there are 2 seperate numbers to be added sepearte from one another. Can someone help. I am a bit of an excel rookie.

 

[Andrew Poulsom]

Welcome to the Board.

If your text entries are in A1:A5:

=SUM(INDEX(LEFT(A1:A5,FIND("/",A1:A5)-1)+0,))

=SUM(INDEX(RIGHT(A1:A5,LEN(A1:A5)-FIND("/",A1:A5))+0,))

 

[bl1nd]

If I had cell D5 with "0600-1430" in it, how would I write a formula in a different cell to take 1430 minus 0600, giving 8.5 as the answer, but also incorporate so that if the result is greater than 5.4, to also subtract 0.5 from the result?

 

[ineil21]

i need help with this, iv been trying to add multiple numbers in a single cell along with 4 more cells.

 

[philswaby]

Is it possible to have multiple numbers i.e. 2,43,8 in a single cell, and show the sum of these numbers in another cell? Also is it possible to add characters IIII together to get 4? Thanks ahead for any help on this matter.

=SUM((LEN(W280)-LEN(SUBSTITUTE(W280,{1,2,3,4,5,6},"")))*{1,2,3,4,5,6}) Substitute any number or symbol where {numbers} are assign these any value