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

Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Thanks Roger.......I use XL97 at work where I developed the solution and am
at home now with XL2k and tried it again and it worked fine.......are you
sure you have the NumLock on when doing the Replace and typing the 010 on
the Keypad?...........did you enter the data with Alt-Enter?.....do you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the solution
and am
at home now with XL2k and tried it again and it worked fine.......are
you
sure you have the NumLock on when doing the Replace and typing the 010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Thanks again for your kind remarks Roger, and certainly no offense taken
that you are experiencing trouble using the solution. I also have had these
kind of problems many times....and it seems "keypad" related, but I'm not
sure why......your SUBSTITUTE formula pretty well shows the character is
there, however, when I entered my number groups, I did not use the Alt-Enter
after the last group, only a straight ENTER....maybe that's the
difference........if you get a chance, maybe try it on a desktop
machine.....could be something to do with your wireless keyboard.

Dinner time here now in St. Petersburg, Florida......

Vaya con Dios,
Chuck, CABGx3





"Roger Govier" wrote in message
...
Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the solution
and am
at home now with XL2k and tried it again and it worked fine.......are
you
sure you have the NumLock on when doing the Replace and typing the 010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Roger, do you have a key somewhere on the board labeled [ Fn ]?
Also, do you have dual purpose keys, where some of the alpha keys also have
numbers on them, perhaps in a different color?

THOSE are the number keys you'll have to use.
On my Dell laptop, I have to hold the <Fn key plus the <Alt key, and THEN
use the dual purpose alpha/number keys.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Roger Govier" wrote in message
...
Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the solution
and am
at home now with XL2k and tried it again and it worked fine.......are
you
sure you have the NumLock on when doing the Replace and typing the 010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Hi RD

Thanks for the response. Yes my notebook does have those keys, and yes,
I do use them if ever I need to use the Notebook keyboard itself.
But here in the office, as I mentioned, I use an external full size
keyboard with numeric pad.
The Alt key is working, as when I type Alt065 I get "A", and Alt097 I
get "a" so I don't think its a function of the keyboard not working.

Just trying something else.
When I type Alt010 in cell A1 in the spreadsheet I get a funny symbol
which when you enter =CODE(A1) it returns 63
When I type ALt010 in the Find dialogue, nothing shows.
Must be something to do with character sets, although I am using Font
Arial size 11.
Definitely must get off to bed now, so I will return to this tomorrow.

If you, or others have any thoughts about the character sets, let me
know.
I now feel pretty convinced that is where the problem lies.

--
Regards

Roger Govier


"RagDyer" wrote in message
...
Roger, do you have a key somewhere on the board labeled [ Fn ]?
Also, do you have dual purpose keys, where some of the alpha keys also
have
numbers on them, perhaps in a different color?

THOSE are the number keys you'll have to use.
On my Dell laptop, I have to hold the <Fn key plus the <Alt key, and
THEN
use the dual purpose alpha/number keys.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------

"Roger Govier" wrote in message
...
Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an
imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the
solution
and am
at home now with XL2k and tried it again and it worked
fine.......are
you
sure you have the NumLock on when doing the Replace and typing the
010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with
ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Hi RD and Chuck

Just to let you both know, Excel had got screwed up somewhere.
Closed everything down last night, and after restarting today,
Find/Replace works OK with Alt010 and +


--
Regards

Roger Govier


"RagDyer" wrote in message
...
Roger, do you have a key somewhere on the board labeled [ Fn ]?
Also, do you have dual purpose keys, where some of the alpha keys also
have
numbers on them, perhaps in a different color?

THOSE are the number keys you'll have to use.
On my Dell laptop, I have to hold the <Fn key plus the <Alt key, and
THEN
use the dual purpose alpha/number keys.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may
benefit !
---------------------------------------------------------------------------

"Roger Govier" wrote in message
...
Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an
imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the
solution
and am
at home now with XL2k and tried it again and it worked
fine.......are
you
sure you have the NumLock on when doing the Replace and typing the
010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with
ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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

Ok Roger, thanks for the feedback, glad you got it working. Also, a
thank-you for bringing the SUBSTITUTE function back to memory. I had seen
it before but have never really used it for anything I needed. I will
certainly keep it in mind more now, thanks to you.

It's amazing really, how these newsgroups can help so many different people
learn so many different things, just from a simple post beginning.

Thanks for starting this one Brad1982

Vaya con Dios,
Chuck, CABGx3






"Roger Govier" wrote in message
...
Hi RD and Chuck

Just to let you both know, Excel had got screwed up somewhere.
Closed everything down last night, and after restarting today,
Find/Replace works OK with Alt010 and +


--
Regards

Roger Govier


"RagDyer" wrote in message
...
Roger, do you have a key somewhere on the board labeled [ Fn ]?
Also, do you have dual purpose keys, where some of the alpha keys also
have
numbers on them, perhaps in a different color?

THOSE are the number keys you'll have to use.
On my Dell laptop, I have to hold the <Fn key plus the <Alt key, and
THEN
use the dual purpose alpha/number keys.
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit !

--------------------------------------------------------------------------
-

"Roger Govier" wrote in message
...
Hi Chick

Using XL2003, Notebook computer with external wireless keyboard where
Numlock is permanently switched on.
Edit Find and Edit Find/Replace both come up with message cannot find
entries you are looking for.
Data entered in A1 with Alt Enter as 123 AltEnter 456 Alt Enter 789
AltEnter (Spaces in typing only, not in actual cell entry.
Cell A1 shows
123
456
789

=CODE(MID(A1,4,1)) returns 10
=SUBSTITUTE(A1,Char(10),"+") returns 123+456+789+

I'm not saying I can't use your solution (which I found an
imaginative
one, well done), it just seems strange that my machine is refusing to
Find and replace Alt010.

Maybe it needs a rest!!! like me, because its 23:15 here in the UK.

--
Regards

Roger Govier


"CLR" wrote in message
...
Thanks Roger.......I use XL97 at work where I developed the
solution
and am
at home now with XL2k and tried it again and it worked
fine.......are
you
sure you have the NumLock on when doing the Replace and typing the
010
on
the Keypad?...........did you enter the data with Alt-Enter?.....do
you have
"CellView" add-in to see what hidden characters are actually there?

Vaya con Dios,
Chuck, CABGx3



"Roger Govier" wrote in message
...
Hi Chuck

Nice solution.
However, for some reason, EditReplace would not work for me with
ALT
010, or SPACE when trying to insert the + signs.
Any ideas why?

--
Regards

Roger Govier


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