Extracting Numbers from text

I have seen another post which is over my head and uses macros to solve a similar problem.

Here is my problem I need to to solve..

A1 contains text "12c 1b 3w" or "12c1b3w" or "1c12b3w" or "12 1" or "6c" or "12b" or "7" or can be blank. 1 to 3 sets of numbers, 1 or 2 digits each.

I would like cell B1 to extract(?) any set (or single) numbers and add them together!

I am using the below formula and It works fine to get the first set of numbers. But I can't figure out how to get the second or third set of numbers (and better yet add the two together)

=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$1000))))

Any help without macros? Thank you much in advance.

vlady that was awesome and worked fine for my example!

But I see now I need to change my example to include two digit numbers also..
See rows 8 and 9 in my new example.
Think it can still be done?

Below info not important to me, just curious about rows

BTW - when i attempt to manipulate your formula in Row 4, the "{" dissapears from the beginning of your formula and then it doesnt add up correctly?

And I don't seem to be able to reinsert it properly as in row 5... Why I wonder?

Thank you vlady

And I now see I'd like to put an "if" statement in front of your formual and the "{" dissapears and the formula doesnt add all the numbers? ARG... lol

try it (array formula)
=SUM(IFERROR(MID(A3;ROW($1:$100);1)/1;0))

copy down

Ghozi thanks.

Only issue is some of my numbers are two digits long as in "12".
And it doesn't work on those.

Also I'd like to nest this in an if statement, can that be done with an array?
Can you look at my new example? (Example -2)

Thank you very much

I guess my example didn't include any double digit numbers like 12 or 17.
There will be no triple digit numbers though. Worked well with my sigle digit example.

I have included a third example using your formula here with double digits.

Also I would like to nest this in an if statement. I did it in rows 13 and 14, it seems to work the same way.

=IF(ISNUMBER(SEARCH(Employee,N7)),"YOUR_ARRAY_FORMULA","")

Thanks, Tim

Marcol this works perfectly!

I will need to school myself on udf instalation and usage. Doesn't look to tuff for this newbie.

Thank you very much.

Thank you Marcol, I appreciate it. This worked somewhat, the only problem I have now is that I only want the numbers inside the ( ) to be totaled. If I add numbers outside of the ( ) it is still including them in the sum. Any ideas?

Hi mstubbs

unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

I answered your first query in this thread because I felt it was relevant to the OPs' question.

You are now asking something quite different, and as the OP has marked the thread [SOLVED], better you start your own thread.

I suggest a title such as "Sum only numbers in brackets in a cell"

PM me when you have done this and I will post the UDF solution I have waiting for you.