Search

  • Thread starter Thread starter Brad1982
  • Start date Start date
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
 
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.
 
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, 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.
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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.
Click to expand...
Click to expand...
 
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 said:
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 said:
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

--------------------------------------------------------------------------
Click to expand...
-
Please keep all correspondence within the NewsGroup, so all may
benefit !
--------------------------------------------------------------------------
Click to expand...
-
Click to expand...
 
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
 
Back
Top