Add All Numbers in a Single Cell

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.

I'm pretty certain that you will need VBA to run a UDF to do this. I can show you how... but if you mean NO VBA... I can only do this with single digit numbers.

this is a monster array formula:

Big(1)small(2)big(2)big(1)	6	{=SUMPRODUCT(1*MID(MID(INT(NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1)/10,""))),1,LEN(A1)),ROW(INDIRECT("1:"&LEN(MID(INT(NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT("1:"&LEN(A1))),1)/10,""))),1,LEN(A1))))),1))}
Carmen14Sarah0Naomi1	6
Big[1]small(2)	3
Big2Big3Medium(2)	7
Color24Big[2]Big[1]	9

btw. digits: 0,1,2,3,4,5,6,7,8,9 and nothing more

There's a much simpler one to add single digits. But in this case, it won't do as the 14 in Carmen14..... counts as 14, not as 1 and 4.

See the expected answers in Post 1...

Once again, the requirement is clearly stated in the opening post, Sandy. The bits in red make it clear that it's whole numbers that are required:

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

However, on reflection, the title is a bit misleading - I will amend it to properly reflect what's needed here.

What's the issue with VBA? It's the only way to do it.... 99.9% certain.

Cross-posted at: https://www.mrexcel.com/forum/excel-...ngle-cell.html
Please read Excel Forum's Cross-Posting policy in rule 8: http://www.excelforum.com/forum-rule...rum-rules.html

Here is with array formula (long one).
Formula must be entered with Ctrl+Shift+Enter

Please Login or Register  to view this content.

v	A	B
1	Big(1)small(2)big(2)big(1)	6
2	Carmen14Sarah0Naomi1	15
3	Big[1]small(2)	3
4	Big2Big3Medium(2)	7
5	Color24Big[2]Big[1]	27

Hahaha. I had gone through the multiple substitutes, but had got stuck converting the array back into a summable string...

Where did you dream that one up, Bosco??? Inspired...

Really nice, Bosco
what about:

Please Login or Register  to view this content.

Result: 1500
(English keyboard only)

WOW! really impressive

edit: it works even with arabic characters ! جمهوری اسلامی5 ایرانx1b4

I bow low and thank you

@Bosco ... WOW!

When I try to figure this one out will it cause nightmares? LOL