Add All Digits in a Single Cell

[richardking]

Hi Guys,


I need your help with a formula. Not VBA. I have a single column of upto 100 rows that contains entries that are alphanumeric and sometimes with symbols.
I would like to add all the numbers that are present in every single entry. One entry is one cell.
Each entry may look like any of the following placed in A1, A2, and so on:


1. Big(1)small(2)big(2)big(1)
2. Carmen14Sarah0Naomi1
3. Big[1]small(2)
4. Big2Big3Medium(2)
5. Color24Big[2]Big[1]


So that if the numbers are added. The result would be:


1. 6
2. 15
3. 3
4. 7
5. 27


Thank you so much.

 

[footoo]

First go to the VBE, Tools/References, put a checkmark against "Microsoft VBScript Regular Expressions 5.5” and click OK.
Then :

Function SumNum(cel As Range)
Dim Reg As RegExp, ray, i%, s%
Set Reg = New RegExp
Reg.Global = True
Reg.Pattern = "\D"
ray = Split(Reg.Replace(cel, ","), ",")
For i = 0 To UBound(ray)
    If IsNumeric(ray(i)) Then s = s + ray(i)
Next
SumNum = s
End Function

 

[richardking]

thanks for taking the time footoo. But I need formulae, not VBA.

 

[JazzSP8]

Check this thread for a few different answers;

https://www.mrexcel.com/forum/excel-questions/640823-sum-all-digits-single-cell.html

 

[footoo]

Sorry, I missed that in your original post.
But once you have installed the Reference and the code, all you have to do is put =SumNum(A1) in B1 and drag down.

 

[richardking]

@JazzSP8 thanks for posting that link. however, theres a big difference between that post and this. the other post has all numbers inside a cell. while this one, is alphanumeric and may even contain symbols.
@footoo thanks. but i already looked that up. i need formulae. not VBA. coz Im going to put this into google sheets

 

[JazzSP8]

@JazzSP8 thanks for posting that link. however, theres a big difference between that post and this. the other post has all numbers inside a cell. while this one, is alphanumeric and may even contain symbols.

Well, no - Post #9 has a formula that works with single digits from mixed alpha numeric cells;

=SUM(IFERROR(1*MID(D6,ROW($1:$99),1),))

But I did misunderstand your post, that formula won't work for "Carmen14Sarah0Naomi1" because it does 1 + 4 + 0 + 1 where you need it do 14+0+1

Unfortunately I don't know how to amend it to work for you

 

[Joe4]

i need formulae. not VBA. coz Im going to put this into google sheets

So, is this really then a Google Sheets question and not an Excel question?
If so, please be sure to mention that in the initial question, and it should not be posted in the "Excel Questions" forum, but rather the "General Excel & Other Questions" forum.

 

[richardking]

@JazzSP8 thanks for pointing that out.
@Joe4 its true that I will be using it for Google sheets, but its also true that I would be needing it for excel. sheets for online. excel for offline and internal usage. hope it still qualifies to be in this section of the forum.

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-general/1238403-add-all-numbers-in-a-single-cell.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html