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

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


--
Regards,
Dave


"DMG" wrote:

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

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


---
Regards,
Norman


"DMG" wrote in message
...
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

That is perfect. Thank you for your help.

"David Billigmeier" wrote:

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


--
Regards,
Dave


"DMG" wrote:

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

Without the need to use SHIFT+CTRL+ENTER,
=SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"DMG" wrote in message
...
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