Search #11

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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 3:26:06 PM
excel.misc 78881 articles. 5 followers. Follow

27 Replies
545 Views

Similar Articles

[PageSpeed] 3

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

0
pashurst (2576)
1/13/2006 4:21:53 PM
Sorry, you need to convert this to a value. Amended formula:

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

Pete

0
pashurst (2576)
1/13/2006 4:24:26 PM
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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 5:12:06 PM
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

0
pashurst (2576)
1/13/2006 5:31:32 PM
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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 5:42:23 PM
This will work for this one cell but it's a lot of maintenance if yo
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(A1,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 o
that you wish to perform this function on, the data has to be identica
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 b
in the hundreds, the others always have to be 2 digits.  If your dat
is not identical say in A2the first line = Green(116).  You hav
increased the number of characters by 1 so all of the formulas abov
need to be adjusted so it would look like.

=SUM(VALUE(MID(A3,FIND("(",A3,1)+1,3))+VALUE(MID(A3,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 eac
is increased by 1.

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

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

HTH

Stev

--
Steve
-----------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=757
View this thread: http://www.excelforum.com/showthread.php?threadid=50109

0
1/13/2006 5:55:12 PM
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(A1,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(A3,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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 6:01:24 PM
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?action=getinfo&userid=7571
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 6:04:30 PM
That is a possibility but would require a complete revamp of 
production schedule. The schedule currently has about 200 SKU's on i
on a daily basis. To keep the size of the schedule down in pag
length(currently 4) we use a 7 font and put all of the SKU's for th
appropriate machine in one cell. 

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

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

--
Brad198
-----------------------------------------------------------------------
Brad1982's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1569
View this thread: http://www.excelforum.com/showthread.php?threadid=50109

0
1/13/2006 6:28:37 PM
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?action=getinfo&userid=15690
> View this thread: http://www.excelforum.com/showthread.php?threadid=501093
> 
> 
0
CLR (807)
1/13/2006 6:45:02 PM
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

0
pashurst (2576)
1/13/2006 7:02:07 PM
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?action=getinfo&userid=15690
> > View this thread: http://www.excelforum.com/showthread.php?threadid=501093
> > 
> > 
0
CLR (807)
1/13/2006 8:03:02 PM
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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 8:07:29 PM
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?action=getinfo&userid=15690
> > > View this thread: http://www.excelforum.com/showthread.php?threadid=501093
> > > 
> > > 
0
CLR (807)
1/13/2006 8:19:02 PM
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),SEARCH("(",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

0
pashurst (2576)
1/13/2006 8:31:14 PM
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),SEARCH("(",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?action=getinfo&userid=15690
View this thread: http://www.excelforum.com/showthread.php?threadid=501093

0
1/13/2006 8:59:59 PM
Thanks for the feedback, glad to be of help.

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

Pete

0
pashurst (2576)
1/13/2006 9:12:29 PM
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" <pashurst@auditel.net> wrote in message
news:1137186749.695453.112510@z14g2000cwz.googlegroups.com...
> Thanks for the feedback, glad to be of help.
>
> I think I might install those free add-ins, Chuck.
>
> Pete
>


0
croberts (1377)
1/13/2006 9:56:47 PM
Hi Chuck

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

-- 
Regards

Roger Govier


"CLR" <CLR@discussions.microsoft.com> wrote in message 
news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
> 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?action=getinfo&userid=15690
>> > > View this thread: 
>> > > http://www.excelforum.com/showthread.php?threadid=501093
>> > >
>> > > 


0
roger5293 (1125)
1/13/2006 10:14:35 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
> Hi Chuck
>
> Nice solution.
> However, for some reason, Edit>Replace would not work for me with ALT
> 010, or SPACE when trying to insert the + signs.
> Any ideas why?
>
> --
> Regards
>
> Roger Govier
>
>
> "CLR" <CLR@discussions.microsoft.com> wrote in message
> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
> > 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?action=getinfo&userid=15690
> >> > > View this thread:
> >> > > http://www.excelforum.com/showthread.php?threadid=501093
> >> > >
> >> > >
>
>


0
croberts (1377)
1/13/2006 10:23:45 PM
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" <croberts@tampabay.rr.com> wrote in message 
news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
>> Hi Chuck
>>
>> Nice solution.
>> However, for some reason, Edit>Replace would not work for me with ALT
>> 010, or SPACE when trying to insert the + signs.
>> Any ideas why?
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
>> > 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?action=getinfo&userid=15690
>> >> > > View this thread:
>> >> > > http://www.excelforum.com/showthread.php?threadid=501093
>> >> > >
>> >> > >
>>
>>
>
> 


0
roger5293 (1125)
1/13/2006 11:15:05 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:O5hKycJGGHA.2708@TK2MSFTNGP11.phx.gbl...
> 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" <croberts@tampabay.rr.com> wrote in message
> news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> > news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
> >> Hi Chuck
> >>
> >> Nice solution.
> >> However, for some reason, Edit>Replace would not work for me with ALT
> >> 010, or SPACE when trying to insert the + signs.
> >> Any ideas why?
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
> >> > 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?action=getinfo&userid=15690
> >> >> > > View this thread:
> >> >> > > http://www.excelforum.com/showthread.php?threadid=501093
> >> >> > >
> >> >> > >
> >>
> >>
> >
> >
>
>


0
croberts (1377)
1/13/2006 11:28:25 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:O5hKycJGGHA.2708@TK2MSFTNGP11.phx.gbl...
> 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" <croberts@tampabay.rr.com> wrote in message
> news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> > news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
> >> Hi Chuck
> >>
> >> Nice solution.
> >> However, for some reason, Edit>Replace would not work for me with ALT
> >> 010, or SPACE when trying to insert the + signs.
> >> Any ideas why?
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
> >> > 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?action=getinfo&userid=15690
> >> >> > > View this thread:
> >> >> > > http://www.excelforum.com/showthread.php?threadid=501093
> >> >> > >
> >> >> > >
> >>
> >>
> >
> >
>
>

0
ragdyer1 (4060)
1/13/2006 11:39:13 PM
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" <RagDyer@cutoutmsn.com> wrote in message 
news:O9V%23RqJGGHA.216@TK2MSFTNGP15.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:O5hKycJGGHA.2708@TK2MSFTNGP11.phx.gbl...
>> 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" <croberts@tampabay.rr.com> wrote in message
>> news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
>> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
>> > news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
>> >> Hi Chuck
>> >>
>> >> Nice solution.
>> >> However, for some reason, Edit>Replace would not work for me with 
>> >> ALT
>> >> 010, or SPACE when trying to insert the + signs.
>> >> Any ideas why?
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> >> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
>> >> > 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?action=getinfo&userid=15690
>> >> >> > > View this thread:
>> >> >> > > http://www.excelforum.com/showthread.php?threadid=501093
>> >> >> > >
>> >> >> > >
>> >>
>> >>
>> >
>> >
>>
>>
> 


0
roger5293 (1125)
1/14/2006 12:00:47 AM
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" <RagDyer@cutoutmsn.com> wrote in message 
news:O9V%23RqJGGHA.216@TK2MSFTNGP15.phx.gbl...
> 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> news:O5hKycJGGHA.2708@TK2MSFTNGP11.phx.gbl...
>> 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" <croberts@tampabay.rr.com> wrote in message
>> news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
>> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
>> > news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
>> >> Hi Chuck
>> >>
>> >> Nice solution.
>> >> However, for some reason, Edit>Replace would not work for me with 
>> >> ALT
>> >> 010, or SPACE when trying to insert the + signs.
>> >> Any ideas why?
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
>> >> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
>> >> > 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?action=getinfo&userid=15690
>> >> >> > > View this thread:
>> >> >> > > http://www.excelforum.com/showthread.php?threadid=501093
>> >> >> > >
>> >> >> > >
>> >>
>> >>
>> >
>> >
>>
>>
> 


0
roger5293 (1125)
1/14/2006 2:23:18 PM
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" <roger@technologyNOSPAM4u.co.uk> wrote in message
news:O2GlTYRGGHA.3176@TK2MSFTNGP12.phx.gbl...
> 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" <RagDyer@cutoutmsn.com> wrote in message
> news:O9V%23RqJGGHA.216@TK2MSFTNGP15.phx.gbl...
> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> > news:O5hKycJGGHA.2708@TK2MSFTNGP11.phx.gbl...
> >> 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" <croberts@tampabay.rr.com> wrote in message
> >> news:%23xrr1$IGGHA.3056@TK2MSFTNGP09.phx.gbl...
> >> > 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" <roger@technologyNOSPAM4u.co.uk> wrote in message
> >> > news:OnkM#6IGGHA.3532@TK2MSFTNGP14.phx.gbl...
> >> >> Hi Chuck
> >> >>
> >> >> Nice solution.
> >> >> However, for some reason, Edit>Replace would not work for me with
> >> >> ALT
> >> >> 010, or SPACE when trying to insert the + signs.
> >> >> Any ideas why?
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "CLR" <CLR@discussions.microsoft.com> wrote in message
> >> >> news:4D076479-D39B-4317-9625-CD296FC88F50@microsoft.com...
> >> >> > 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?action=getinfo&userid=15690
> >> >> >> > > View this thread:
> >> >> >> > > http://www.excelforum.com/showthread.php?threadid=501093
> >> >> >> > >
> >> >> >> > >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
>
>


0
croberts (1377)
1/14/2006 2:34:11 PM
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(SUBSTITUTE(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(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"f",""),"g",""),"h",""),"i",""),"j",""),"k",""),"l",""),"m","")

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

F1:
="="&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(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

0
pashurst (2576)
1/14/2006 7:47:11 PM
Reply:

Similar Artilces:

Advanced search using ?
Im using both Outlook 2000 and 2003. In one of my folders Im collecting emails classified as spam. Know here is the problem, I have to filter the emails to see who are spam and who arent. Some emails are tagged with a number of ?marks. So I whant to be able to look for them and delete those messages. Are there any good thirdparty utilities that I can use (Im under the impretion that OU cant do searchs with ? and *) Rgds Ramzell <trkoping@hotmail.com> wrote: > Are there any good thirdparty utilities that I can use (Im under the > impretion that OU cant do searchs with ? and *) ...

Outlook attempting to access COM component 'Outlook.Application.11'
Hi, I am running HIPS software, and am getting a lot of complaints when I attempt to copy and paste sections of emails into other mails and copying from email sections to another program. I cannot find any information that makes sense to me (lots of stuff on 'InvokeVerb2 method' etc) hmmm. Could anybody advise when this particular action happens and if it is only linked to copying and pasting (could it be used in any way by viruses, worms etc?) Any help appreciated. Darren Mease <darren.mease@boxingorange.com> wrote: > I am running HIPS software, Care to define w...

Item search
When I select Edit Item in Store Ops Manager>Item, is it possible to add more columns to the search field? Rather than having to go into find? If not, is this a new feature in version 2 or 3 of RMS? Thanks ...

Advanced Find Search on Bit Field
Hello- I am trying to search on a bit field within CRM 3.0 and its not an option in the drop down on the advanced find form. Does anyone know how to search on a bit field? i.e. does not allow bulk email? This is important for us because we wanted to pull all of our accounts that are Approved and I have it set up as a bit field. Hi Tiffani, You should be able to use bit fields in advanced find. In the list there is "Do Not Allow E-mail" as well as "Do Not Allow Fax" and "Do Not Allow Phonecalls. Hope this helps, -- Ronald Lemmen - MSCRM MVP Avanade Netherlan...

Cancelling a block search & replace
I have not been able figure out how to cancel a search and replace for a range of cells. When I hit the cancel button the search and replace only jumps to the next cell to check. For a large block of cells it can be quite tedious to have to hit the cancel button repeatedly. I'm sure there must be an easier way. However,I have not been able to find any other reference to this problem ...

Search by Text String Question
If column a contains a word contained in a list from a table I want another column to list that word. Example Table Data Comments Red Yellow Gray Blue Green White Example Table List Red Green Query Comments KeyWord Red Yellow Gray Red Blue Green White Green Try this -- SELECT [Table Data].Comments, [Table List].KeyWord FROM [Table Data], [Table List] WHERE ((([Table Data].Comments) Like "*" & [KeyWord] & "*")); -- KARL DEWEY Build a little - Test a little...

printing 8.5 x 11 booklets on 11x17 paper
I'm having great difficulty printing 8.5x11 booklets on 11x17 inch paper(that will be folded in half.) I've gotten all the way to the correct way it's suppose to look in print preview, but as soon as i press print, the page lay out changed automatcally. My text and graphics stay oriented the way i had them, but the back white page or "paper" visible on the screen behind my text and graphic changes and prints that way. The actual text prints off center. Is there something i'm doing wrong in the set up? I hope this is visible, either my server is down or I di...

Report error 11-18-03
Hello everyone, I am receiving this error : File frs://Input/a_176/000/000/176/9883277fdf1b814.rpt not found on File Repository Server. [On Cache/Page Server: CRM.pageserver] Do anyone know solution to this error Regards, Vladimir which report are you trying to run? "vladimir" <vladimirk@generation5.net> wrote in message news:0abf01c3ae09$33ab2a20$a601280a@phx.gbl... > Hello everyone, > I am receiving this error : > File frs://Input/a_176/000/000/176/9883277fdf1b814.rpt not > found on File Repository Server. [On Cache/Page Server: > CRM.pageserver] > Do a...

Unread Mail Search Folder continues Disappearing
Hello, I am currently being told by one of my clients that their Unread Mail Search Folder continues to disappear on their favorites folder. When I search in the Search Folders section I do not see the Unread Mail folder. I place rthis folder back and the user continues to use outlookk normally for a couple of days and again they seem to have lost their Unread Mail Search Folder. Can anyone assist me on this issue. ...

Outlook Contacts Only Not Showing up in Search
After an install (and uninstall of Offic 2010 Beta, I am no longer able to located my contacts. I have made sure that the Outlook files are selected for insexing, ran scanpst, and re-indexed everything. Still, the kind:contact searches yield nothing. It had worked fine previously. Any ideas? Submitted via EggHeadCafe - Software Developer Portal of Choice WPF Custom Validation Using the Enterprise Library http://www.eggheadcafe.com/tutorials/aspnet/9af7ac1a-d7f3-4e00-9aec-33ef1ec7d1a3/wpf-custom-validation-usi.aspx Only if you state how you reinstalled Outlook, connected it you you...

Search a worksheet, extract rows using a list from another sheet
I am trying to use a list of names (first, last) in a colum in worksheetA to find corresponding entries in an imported worksheet B, Once I find the name in worksheet B I want to extract the entire row of data into another new worksheet. For example Worksheet A: Column A Bill Jones Fred Smith Worksheet B: Place Overall Name Swim Bike Run Finish 1 12 Bill Jones 24 anytown ST 13:45 54:45 23:00 1:34:45 4 78 Fred Smith 56 anothertown ST 15:00 56:12 24:34 1:48:34...

Search #4
Dear All, I want to find a mail in all subfolders but i get an error The folder you selected does not let you search other folders at the same time. To search other folders, clear the check box next to this folder how can i search at a time. TIA Searching multiple folders only works in the default store, not public folders. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, kiran asked: | Dea...

COPY FROM SEARCH
IS THERE A WAY TO COPY THE RESULTS IN TEXT FORMAT FROM WINDOWS EXPLORER OR A SEARCH TO EXCEL? In Internet Explorer, click and drag over the search results to highlight, press CTRL+C to copy to clipboard. In Excel, right click in desired cell, select Paste Special... and select Text from the AS: list and click OK. HTH "CH" wrote: > IS THERE A WAY TO COPY THE RESULTS IN TEXT FORMAT FROM > WINDOWS EXPLORER OR A SEARCH TO EXCEL? > ...

Search Form
I created a Search form to search some fileds in my main database form. The only problem is that one of the fields has many entries with apostrophes ('). How do I compensate for that? It depends on how you're doing your searches. If you're using single quotes as text delimiters in your queries or code, you need to either change to using double quotes, or else use the Replace function around the input values to change any occurrance of single quotes to two consecutive single quotes. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "gu...

Newsreader #11
I'm using Office 2003 on a WinXP machine. Right now the newsreader function is not available on Outlook and I wanted to install it. OL help tells me that I have to install it using 'Add or Remove Buttons': However, under the Categories:Go I do not seem to have 'News' as a command. What am I missing here? There are a few thousand posts in this group that answer this question. Did you look? To get the News command back, go back to the Programs tab in Control Panel | Internet Options and change the default newsreader to Outlook Express. If OE prompts you to make it your ...

Most difficult Outlook 2007 question "Search subfolders"
In Outlook 2003, "Advanced Find" used to allow the searching of subfolders in a shared mailbox (not a public folder, that I understand.) In Outlook 2007, I cannot search the same mailbox's subfolders that I used to be able to search in Outlook 2003. Is this permissions? Removed functionality? I do have "Instant Search" installed but it only allows searching on my personal mailbox. I do not have the option to add the shared mailbox to the "Instant Search" options. I've googled and googled. Nothing. I think at least a handful of people are looking fo...

Web client 03-11-05
We have a client with a mix of Windows 2000 and Windows XP machines. All 15 computers have no problem opening and using the web client, APART from 2 machines, one XP and one 2000. The entire network is on one domain and runs through one switch. The problem pc's can use all network resources and log on correctly and browse the web. The firewall/pop-up securities are identical on all pc's as they recieve these settings off the server via a group policy. When you type the host header 'crm' in the bottom left of the window you see it referancing http://crm and then it goes ...

Funny! More offers to select a search engine!
www.goodugle.com ...

money essentials #11
I went online and purchased the program, when the download button came up I clicked on it, it gave the the form for rebates, then I never received a pop window to download. I kept trying and then received msgs that I have went over my 5 tries, I have looked all over to see if it downloaded and don't see it anywhere. what else can i do to get the download? On 3 ago, 15:03, bt <b...@discussions.microsoft.com> wrote: > I went online and purchased the program, when the download button came up I > clicked on it, it gave the the form for rebates, then I never received a pop >...

search to find all temp files
I did a *.tmp,*.chk,~*.* search to find all temp files on my pc. Apparently, the search found files that are not on my pc, because I had hundreds of files that could not be deleted due to Windows saying: "Cannot delete file: Cannot read from source file or disk." What gives? Jack In news:uyt68Vp0KHA.4832@TK2MSFTNGP04.phx.gbl, Jack B <jslimp01nospam@earthlink.net> typed: > I did a > *.tmp,*.chk,~*.* > search to find all temp files on my pc. > > Apparently, the search found files that are not on my pc, > because I had hundreds of f...

Search and Replace #2
Is there a way in Excel to do a replace that will apply bold to text, the way you can in Word. Is there a code I would use in the Replace field to signify bold. Thanks Not built into excel. You could use a macro, though: Option Explicit Sub testme01() Dim myWords As Variant Dim myCell As Range Dim myRng As Range Dim FirstAddress As String Dim iCtr As Long Dim letCtr As Long On Error Resume Next Set myRng = Intersect(Selection, _ Selection.Cells _ .SpecialCells(xlCellTypeConstants, xlTextValues)...

Look for: Search In does not reveal in which folder an email is found located
When I search for an old email using Outlook's Look for: box and specify which folders to search in via the "Choose folders=>Search subfolders" , I am unable to see in which folder a particular email was found from the resulting list of emails. How can I tell in which folder the email I'm looking for was located? -- Regards, Peter Sale Santa Monica, CA USA To email me, just pull 'my-leg.' ...

Mail #11
Hi, Our users have pst files in our Outlook folders list. These are not really archive files, they are personal folders that users have carried over with them from a previous setup. These pst files are being emptied during automatic archiving, which seems normal, depending on the cut-off date specified in the Tools/Options etc. The problem is that the contents are not being stored into the user's local disk pst file specified in the autoarchive parameters. The mail from these pst files is disappearing. At the end of the auto archive, the mail is neither in the original pst nor in t...

Windows Player 11
I asked a question simply cause I'm " Naive " to these machines. I don't know the 1rs thing about 'em. I need it for my classes.I'm just asking for your help??? -- Currently Lost in The World of Computers ~ Sweetie ~ Message posted via WindowsKB.com http://www.windowskb.com/Uwe/Forums.aspx/windows-media-player/201005/1 On Tue, 25 May 2010 07:52:51 GMT, "Sweetie via WindowsKB.com" <u59250@uwe> wrote: > >I asked a question simply cause I'm " Naive " to these machines. I don't know >the 1rs thing about &#...

Searching using an address String
I have a database that seperates the elements of an address into seperate fields. Street Prefix, Low Street Number, High Street Number, Street Directional, Street Name, Street Type. I would like to have users enter an address as single string, then use that string to search the database. I need help with this. Thanks If they enter one string then you would need to parse that into its separate parts - a difficult if not impossible task to accomplish accurately to set up accurate criteria. You could try something like the following but that would require you to match all the Fields and...