Summing the digits in Excel cells (long and short strings)

EDIT

Based on your explanation your comments, it seems that what you want is what is called the digital root of the all the digits (excluding the decimal point). In other words, repeatedly summing the digits until you get to a single digit.

That can be calculated by a simpler formula than adding up the digits.

=1+(SUBSTITUTE(B5,".","")-1)-(INT((SUBSTITUTE(B5,".","")-1)/9)*9)

For long numbers, we can split the number in half and process each half. eg:

=1+MOD(1+MOD(LEFT(SUBSTITUTE(B5,".",""),INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)+1+MOD(RIGHT(SUBSTITUTE(B5,".",""),LEN(SUBSTITUTE(B5,".",""))-INT(LEN(SUBSTITUTE(B5,".",""))/2))-1,9)-1,9)

However, the numbers should be stored as TEXT. When numbers are stored as numbers, what we see may not necessarily be what is stored there, and what the formula (as well as the UDF) will process.

The long formula version will correct all the errors on your worksheet EXCEPT for B104. B104 appears to have the value 5226.9332653096000 but Excel is really storing the value 5226.9333265309688. Because of Excel's precision limitations, this will get processed as 5226.93332653097. Hence there will be a disagreement.

Another method that should work would be to round all of the results in your column B to 15 digits (eg: . Combining that with using the long formula version should result in agreement for all the values you show.

Explanation

• if a number is divisible by 9, its digital root will be 9, otherwise, the digital root will be n MOD 9

• The general formula would be: =1+Mod(n-1,9)

  • In your case, since we are dealing with numbers larger than can be calculated using the MOD function, we need to both remove the dot, and also use the equivalent of mod which is n-(int(n/9)*9)
• Notes:
  • this will work best with numbers stored as text. Since Excel may display and/or convert large numbers, or numbers with many decimal places, differently than expected, working with text strings of digits is the most stable method.
  • this method will not work reliably with numbers > 15 digits.

If you have numbers > 15 digits, then I suggest a VBA User Defined Function:

Option Explicit
Function digitalRoot(num As String) As Long
    Dim S As String, Sum As Long, I As Long
S = num
Do While Len(S) > 1
    Sum = 0
    For I = 1 To Len(S)
        Sum = Sum + Val(Mid(S, I, 1))
    Next I
    S = Trim(Str(Sum))
Loop

digitalRoot = CLng(S)

End Function

I hope you are looking for something like following UDF.

Function SumToOneDigit(myNumber)
Dim temp: temp = 0
CalcLoop:

For i = 1 To Len(myNumber)
If IsNumeric(Mid(myNumber, i, 1)) Then temp = temp + Mid(myNumber, i, 1)
Next

If Len(temp) > 1 Then
myNumber = temp
temp = 0
GoTo CalcLoop
End If

SumToOneDigit = temp
End Function

UDF (User Defined Functions) are codes in VBA (visual basic for applications).

When you can not make calculations with Given Excel functions like ones in your question, you can UDFs in VBA module in Excel. See this link for UDF .. If you dont have developer tab see this link ,, Add a module in VBA in by right clicking on the workbook and paste the above code in that module. Remember, this code remains in this workbook only. So, if you want to use this UDF in some other file your will have to add module in that file and paste the code in there as well. If you are frequently using such an UDF, better to make add-in out of it like this link

You could use a formula like:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(A1," ","</s><s>")&"</s></t>","//s"))

You might need an extra SUBSTITUTE for changing . to , if that's your decimal delimiter:

=SUMPRODUCT(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,".",",")," ","</s><s>")&"</s></t>","//s"))

However, maybe a UDF as others proposed is also a possibility for you. Though, something tells me I might have misinterpreted your question...

In addition to using "Text to Columns" as a one-off conversion, this is relatively easy to do in VBA, by creating a user function that accepts the data as a string, splits it into an array separated by spaces, and then loops the elements to add them up.

Add the following VBA code to a new module:

Function fSumData(strData As String) As Double
    On Error GoTo E_Handle
    Dim aData() As String
    Dim lngLoop1 As Long
    aData = Split(strData, " ")
    For lngLoop1 = LBound(aData) To UBound(aData)
        fSumData = fSumData + CDbl(aData(lngLoop1))
    Next lngLoop1
fExit:
    On Error Resume Next
    Exit Function
E_Handle:
    MsgBox Err.Description & vbCrLf & vbCrLf & "fSumData", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume fExit
End Function

Then enter this into a cell in the Excel worksheet:

=fSumData(A1)

Regards,

The UDF below will return the sum of all numbers in a cell passed to it as an argument.

Function SumCell(Cell As Range) As Double

    Dim Fun     As Double           ' function return value
    Dim Sp()    As String           ' helper array
    Dim i       As Integer          ' index to helper array

    Sp = Split(Cell.Cells(1).Value)
    For i = 0 To UBound(Sp)
        Fun = Fun + Val(Sp(i))
    Next i

    SumCell = Fun
End Function

Install the function in a standard code module, created with a name like Module1. Call it from the worksheet with syntax like =SumCell(A2) where A2 is the cell that contains the numbers to be summed up. Copy down as you would a built-in function.