Ok, first delete all TEXT characters using ASAP Utilities
Then delete the leading and trailing parenthesis using ASAP Utilities
Then do Edit Replace and in the "Replace" window hold down the ALT key
and type 010 on the keypad with the NumLock on and in the "Replace with"
window put a + sign....and do replace all
Then add an = sign to the front of the string and perss enter.....it will
sum the numbers

Vaya con Dios,
Chuck, CABGx3


"CLR" wrote:

If anyone knows how to do FindAndReplace for the CHAR(10) character, to
replace it with + signs, after stripping it as I described in my previous
post, then just add an equal sign to the front and your got the sum of the
cell regardless of how many there are..............

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

ASAP Utilities, a free Add-in available at www.asap-utilities.com has
features that will strip all the TEXT characters and parenthesis out of the
cell, leaving just the numbers separated by the CHAR(10)
character...........I tried then using the TEXT functions to sum the numbers
but it gets unwieldly quickly, and if you have as many as 20 in a cell, it
would be even more difficult, perhaps exceeding the legal number of
characters in a formula. But maybe this avenue might give someone else an
idea how to sum the numbers...........of course splitting them out with Data
TextToColumns would make it easier, but the OP has reasons not to want to
do it that way..............

Vaya con Dios,
Chuck, CABGx3





"Brad1982" wrote:


This is way beyond my skill level so I decided to come here first.

I need to search within a cell and add up all of the numbers. The
problem is the numbers lie within text

A1 contains this:
Green(17)
Red(12)
Blue(16)
Orange(230)

So the search function would add up all the numbers of the cell and
return 275


--
Brad1982
------------------------------------------------------------------------
Brad1982's Profile: http://www.excelforum.com/member.php...o&userid=15690
View this thread: http://www.excelforum.com/showthread...hreadid=501093