summing characters in a string

[Guest]

Hi
Is there a way to sum the characters in a string. For example A1 contains
12345. I would like a formula to return 15 (1+2+3+4+5).
As an added twist, the string is variable length.

Thanks

 

[Guest]

Assume your cell to use is A1, this formula will work. Enter it using
CTRL+SHIFT+ENTER as it is an array formula:

=SUM(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

 

[Norman Jones]

Hi DMG,

You could use a user defined function (UDF):

'=============>>
Public Function SumChars(aCell As Range)

Dim i As Long
Dim vVal As Variant

vVal = aCell.Value

If Not IsError(vVal) Then
For i = 1 To Len(aCell)
If IsNumeric(Mid(vVal, i, 1)) Then
SumChars = SumChars + CLng(Mid(vVal, i, 1))
End If
Next i
Else
SumChars = CVErr(xlErrNA)
End If

End Function
'<<=============

Usage: =SumChars(A1)

If you are not familiar with UDFs, you may wish to visit David McRitchie's
'Getting Started With Macros And User Defined Functions' at:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

That is perfect. Thank you for your help.

Assume your cell to use is A1, this formula will work. Enter it using
CTRL+SHIFT+ENTER as it is an array formula:

=SUM(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

 

[Bernard Liengme]

Without the need to use SHIFT+CTRL+ENTER,
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
best wishes