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

Assuming your values are in A1 to A4, type this formula into B1 and
copy down:

=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)

Sum these values to get 275.

Hope this helps,

Pete

Sorry, you need to convert this to a value. Amended formula:

=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))

Pete

Pete Wrote:
Sorry, you need to convert this to a value. Amended formula:

=VALUE(MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1))

Pete

All of the values are in one single cell, not A1 to A4. That is what is
stumping me actually :).


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

Sorry (again), I misunderstood. I'll have a think while I have
something to eat.

Do you have similar entries in A2 and down, or is the problem just a
one-off?

Pete

I havent gotten any real working formulas myself in the hour or so I put
into it.

I have several cells like this I would like to calc for.

The reasoning behind this is a schedule vs capacity sort of thing.

Rather then add up the individual values in a cell I would just like to
have something calc them for me. It also takes out the human error
problem.

Thanks for your help Pete! I hope you can figure something out :-)


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

This will work for this one cell but it's a lot of maintenance if you
have the need to sum the same type of data in A2, A3 and A4.

=SUM(VALUE(MID(A1,FIND("(",A1,1)+1,2))+VALUE(MID(A 1,FIND("R",A1,1)+FIND("(",A1,1)-2,FIND(")",A1,1)-7))+VALUE(MID(A1,FIND("B",A1,1)+5,FIND(")",A1,1)-7))+VALUE(MID(A1,FIND("O",A1,1)+7,FIND("(",A1,1)-3)))

You can't just copy this down. If you have data in A2, A3 and so on
that you wish to perform this function on, the data has to be identical
meaning in A2 it needs to be in the same order (Green,Red,Blue,Orange)
and have the same type of numeric value i.e. Orange always needs to be
in the hundreds, the others always have to be 2 digits. If your data
is not identical say in A2the first line = Green(116). You have
increased the number of characters by 1 so all of the formulas above
need to be adjusted so it would look like.

=SUM(VALUE(MID(A3,FIND("(",A3,1)+1,3))+VALUE(MID(A 3,FIND("R",A3,1)+FIND("(",A3,1)-2,FIND(")",A3,1)-8))+VALUE(MID(A3,FIND("B",A3,1)+5,FIND(")",A3,1)-8))+VALUE(MID(A3,FIND("O",A3,1)+7,FIND("(",A3,1)-4)))

Notice the last number that you are adding to the FIND function in each
is increased by 1.

If Blue changes to 3 characters, you only have to change from the 3rd
formula on.

In any event, like I said, it's a lot of maintenance.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=501093

SteveG Wrote:
This will work for this one cell but it's a lot of maintenance if you
have the need to sum the same type of data in A2, A3 and A4.

=SUM(VALUE(MID(A1,FIND("(",A1,1)+1,2))+VALUE(MID(A 1,FIND("R",A1,1)+FIND("(",A1,1)-2,FIND(")",A1,1)-7))+VALUE(MID(A1,FIND("B",A1,1)+5,FIND(")",A1,1)-7))+VALUE(MID(A1,FIND("O",A1,1)+7,FIND("(",A1,1)-3)))

You can't just copy this down. If you have data in A2, A3 and so on
that you wish to perform this function on, the data has to be identical
meaning in A2 it needs to be in the same order (Green,Red,Blue,Orange)
and have the same type of numeric value i.e. Orange always needs to be
in the hundreds, the others always have to be 2 digits. If your data
is not identical say in A2the first line = Green(116). You have
increased the number of characters by 1 so all of the formulas above
need to be adjusted so it would look like.

=SUM(VALUE(MID(A3,FIND("(",A3,1)+1,3))+VALUE(MID(A 3,FIND("R",A3,1)+FIND("(",A3,1)-2,FIND(")",A3,1)-8))+VALUE(MID(A3,FIND("B",A3,1)+5,FIND(")",A3,1)-8))+VALUE(MID(A3,FIND("O",A3,1)+7,FIND("(",A3,1)-4)))

Notice the last number that you are adding to the FIND function in each
is increased by 1.

If Blue changes to 3 characters, you only have to change from the 3rd
formula on.

In any event, like I said, it's a lot of maintenance.

HTH

Steve
The values of the colors are almost never the same and the colors are
always different. Some days it might even be BlueGray or
LightOrange(283982). I will try messing around with that formula and
see if I can come up with anything. Thanks!


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

How about converting it to columns first, then applying formulas to
those. That way, the formula is always looking at one value rather
than 4 or 5 which makes this cumbersome. You can always do this and
hide those columns when you are done.

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=501093

That is a possibility but would require a complete revamp of a
production schedule. The schedule currently has about 200 SKU's on it
on a daily basis. To keep the size of the schedule down in page
length(currently 4) we use a 7 font and put all of the SKU's for the
appropriate machine in one cell.

Because some days a certain line might have 1 or even 20 SKU's in its
box having columns for each individual SKU may not be possible. Having
it all in one cell means you would just adjust the cell height and
width for a quick fix to more or less SKU's.

I had just assumed this would be an easy formula for the excel masters
here but it looks like it may be very difficult.


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

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

Rather than do Text to Columns, then, can the data be split onto
separate rows, so that my first interpretation of what you require
would be valid?

If you are saying that there could be a variable number of colours
within these cells, then it will be very difficult to pick them out and
sum them in a single formula, though you could do it with a macro.

Is there always at least a single space after each close-bracket symbol
(except for the end)? We could then look for ") " to determine the
number of entries in each composite cell. Is there any restriction on
the number of columns we can use? If there are 4 entries we could use 4
adjacent cells for each number and another for the sum, so what is the
maximum number of different entries that you could expect in one of
these composite cells?

Could you tell me what SKUs are?

Pete

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

It is not just one space after the color each time. It is always enough
spaces to make the word wrap put it to its own line when viewing a
paper document of the spreadsheet. So this would vary based on how long
an the word is. Green would have more spaces after it then Orange would
because it has less letters.

GREEN(240) RED(96)
ORANGE(192)
CLAY(240)

Here is an exact paste of one of my schedule cells. The spacing is
never the same. The only consistant thing is that the number is always
in enclosed like this (number).

"Could you tell me what SKUs are?

Pete"

SKU's = stock keeping unit, in this case they would be the colors
Green, Red, Orange, etc...


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

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

I managed to do this by using a separate sheet, though it's not very
pretty. Assuming the data is in Sheet1, insert a new sheet - I assume
this is Sheet2. To help keep track of what's going on, I used headings
in row1 of Sheet 2, as follows:

B1 = Sum, C1 = Count, D1 = Start, then in E1 to X1 I filled the
sequence 1 to 20, and in Y1 to AR1 I also put the sequence 1 to 20.
Then the following formulae:

B2: =SUM(Y2:AR2)
C2: =COUNT(E2:X2)
D2: 0
E2: =SEARCH(") ",TRIM(Sheet1!$A1)&" ",D2+1)

This is copied across to X2

Y2:
=IF(ISERROR(E2),0,VALUE(MID(TRIM(Sheet1!$A1),SEARC H("(",TRIM(Sheet1!$A1),D2+1)+1,E2-SEARCH("(",TRIM(Sheet1!$A1),D2+1)-1)))

This is copied across to AR2.

The cells B2 to AR2 can then be copied down for as many rows as
necessary.

The following formula is entered in Sheet1 Cell B1 (i.e. next to the
cell we want to analyse):

=Sheet2!B2

and this is copied down as necessary.

I tested it with this in Sheet1 A2:

black(0) brown(1) red(2) orange(3) yellow(4) green(5) blue(6) indigo(7)
violet(8) white(9) purple(10)

and with this in A3:

black(0) brown(1) red(2) orange(3) yellow(4) green(5) blue(6) indigo(7)
violet(8) white(9) purple(10) cyan(11) magenta(12) turquoise(13)
puce(14) ochre(15) pink(16) lightgrey(17) silver(18) gold(19)

It doesn't matter if you have multiple spaces between colours, or at
the end of the string. It will cater for up to 20 colours - you get
#VALUE errors in columns E to X if there are fewer, though this doesn't
matter. The number of colours is counted in column C of Sheet2.

Bit of a sledgehammer to crack a nut, but ...

Hope this helps.

Pete

Pete Wrote:
I managed to do this by using a separate sheet, though it's not very
pretty. Assuming the data is in Sheet1, insert a new sheet - I assume
this is Sheet2. To help keep track of what's going on, I used headings
in row1 of Sheet 2, as follows:

B1 = Sum, C1 = Count, D1 = Start, then in E1 to X1 I filled the
sequence 1 to 20, and in Y1 to AR1 I also put the sequence 1 to 20.
Then the following formulae:

B2: =SUM(Y2:AR2)
C2: =COUNT(E2:X2)
D2: 0
E2: =SEARCH(") ",TRIM(Sheet1!$A1)&" ",D2+1)

This is copied across to X2

Y2:
=IF(ISERROR(E2),0,VALUE(MID(TRIM(Sheet1!$A1),SEARC H("(",TRIM(Sheet1!$A1),D2+1)+1,E2-SEARCH("(",TRIM(Sheet1!$A1),D2+1)-1)))

This is copied across to AR2.

The cells B2 to AR2 can then be copied down for as many rows as
necessary.

The following formula is entered in Sheet1 Cell B1 (i.e. next to the
cell we want to analyse):

=Sheet2!B2

and this is copied down as necessary.

I tested it with this in Sheet1 A2:

black(0) brown(1) red(2) orange(3) yellow(4) green(5) blue(6)
indigo(7)
violet(8) white(9) purple(10)

and with this in A3:

black(0) brown(1) red(2) orange(3) yellow(4) green(5) blue(6)
indigo(7)
violet(8) white(9) purple(10) cyan(11) magenta(12) turquoise(13)
puce(14) ochre(15) pink(16) lightgrey(17) silver(18) gold(19)

It doesn't matter if you have multiple spaces between colours, or at
the end of the string. It will cater for up to 20 colours - you get
#VALUE errors in columns E to X if there are fewer, though this
doesn't
matter. The number of colours is counted in column C of Sheet2.

Bit of a sledgehammer to crack a nut, but ...

Hope this helps.

Pete

awesome!!!!!! Pete you rock!

CLR I also installed those addins and already can see some uses for
some of the functions. Thanks a ton guys!


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

Thanks for the feedback, glad to be of help.

I think I might install those free add-ins, Chuck.

Pete

Yeah, they're pretty neat Pete, I use them a lot.........incidently, in my
description of how to use them for this problem, I've found that one can
eliminate the step of deleting the parenthesis, (duh), and just go ahead
and add the numbers up anyway.....

Vaya con Dios,
Chuck, CABGx3


"Pete" wrote in message
oups.com...
Thanks for the feedback, glad to be of help.

I think I might install those free add-ins, Chuck.

Pete

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

Using Chuck's methodology and standard worksheet functions only (mainly
SUBSTITUTE), I have developed another solution for this. Because of the
limit of 8 nested functions, I have had to split the formulae into 4,
so I have used a second sheet as before. In Sheet2, enter the
following:

C1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(LOWER(TRIM(Sheet1!A1)),")
",")+"),"a",""),"b",""),"c",""),"d",""),"e","" )

(innermost substitute is ")+" for ") " - may get awkward line-break
after posting because of the space)

D1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"f",""), "g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m ","")

E1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D1,"n",""), "o",""),"p",""),"q",""),"r",""),"s",""),"t",""),"u ","")

F1:
="="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(E1,"v",""),"w",""),"x",""),"y",""),"z"," ")

B1: =F1

Copy these down as necessary. The formula in Sheet1 B1 is as before,
i.e.

=Sheet2!B2, copied down as necessary.

As well as the formulae, there is a procedure - Highlight cells in
column B of Sheet2, <copy then Edit | Paste Special | Values | OK and
<enter. With the highlight still on those cells, Data | Text to
Columns then click Finish. This is why I've used column B - the main
formulae are still preserved.

This gives the sum of the numbers in each cell. It will cater for
multiple spaces because of the TRIM function, and will also cater for
more than 20 colours in the original cell. Let's hope there are no
punctuation symbols (like "-") used in the text.

As Chuck says in an earlier post, some threads just grab your attention
and seem to have a life of their own.

Pete