You're still trying to solve this "old" problem?
To start from scratch:
Case 1:
For "regular" spaces, follow Myrna's suggestion:
Select the column of names, then,
<Edit <Replace,
In the "Find What" box, hit the <Space bar 2 times,
In the "Replace With" box, hit the <Space bar 1 time.
Then, "Replace All".
*Repeat* this a couple of times, where you enter 3 and then 4 spaces in the
"Find What" box, always replacing with a single space.
See if this helps the situation to *any* extant.
If some do match, but not all, you might have to repeat, using more and more
spaces in the "Find What" box.
Case 2:
If there's no improvement replacing "regular" spaces (Char(32)), try
replacing "non-breaking" (Char(160)) spaces.
Naturally, you *can't* use the <Space bar for these.
Make sure the "Find What" box is empty.
It tends to "remember" the criteria from your last search, and of course,
you can't see those spaces you entered.
Now, enter the "non-breaking" space in the "Find What" box by:
Hold <Alt,
And type the number,
0160
Using the Num keypad, *not* the numbers under the function keys.
And of course, you will not see anything in the "Find What" box, since those
keystrokes DO produce a space.
Again, enter your single "regular" space in the "Replace" box.
If this doesn't help, and you're still having a problem after all this, you
could try to identify what character is exactly between the names.
Try this formula:
=CODE(MID(A1,5,1))
Where A1 is the cell containing one of the "problem" names, and the "5" is
the character count of the first space in the name, counting from the left.
If you had a problem name of George Washington in cell G5, you would revise
the formula to:
=CODE(MID(G5,7,1))
This should return a "32" for a normal space,
Or a "160" for a non-breaking space.
If it looks like there is more then a single space, just increment the
number in the formula to test those "other" spaces.
AND, if you do get another number returned, just use that number in the
"Edit & Replace" procedure, making sure that you do use *4* digits, with
leading zeroes where necessary.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"George A. Yorks" .(donotspam) wrote in message
...
In using find and replace, I select the full column do ctrl. h to get find
and replace but still trying to figure how to look for three spaces in
find
and two spaces in replace. If I use * * that is all that gets replaced
--
George
"George A. Yorks" wrote:
I am still not able to adjust space between two words. I have a column
of
names with two or three spaces between first and last name. I want to
adjust
so there is only one space between the two names. How to do this. Was
suggested I do a search and replace but have not found how to do this.
Any
help appreciated
--
George
"George A. Yorks" wrote:
Have looked through help menu for how to perform a search and replace
you
speak of. I can't find anything. Could you give me some direction.
Thanks
for all your help
"Myrna Larson" wrote:
If you sometimes have double or triple spaces, you can do a search
and replace
on column AX: search for two spaces and replace with 1 space. Repeat
until
Excel tell you there are no more matches.
On Mon, 7 Mar 2005 20:17:02 -0800, George A. Yorks
.(donotspam) wrote:
In using the lookup formula I find that the data in column AX3 is
not exactly
like that in sheet 2 B4. ie: the spacing between first and last
name is not
the same. Any way to correct this. I only know of the differences
when the
dollar amounts are not tranfered. I then have to correct these
errors
manually. Hope this makes sense
Thanks for all the help
"George A. Yorks" wrote:
I tried this formula and get a "not found" also this is how the
formula
gets
entered. Don't know why. It was entered as below.
=IF(ISNA(MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18,0)),"not
found",INDEX([GEO.xls]Sheet2!$Q$4:$Q$18,MATCH(AX3,[GEO.xls]Sheet2!$B$4:$B$18
,0)))
"RagDyeR" wrote:
Try this in Ay3 of "Miriam":
=IF(ISNA(MATCH(AX3,Geo!$B$4:$B$18,0)),"Not
Found",INDEX(Geo!$K$4:$K$18,MATCH(AX3,Geo!$B$4:$B$ 18,0)))
You can then *double click* on the "fill handle" in the lower
right
corner
of AY3, which will *automatically* copy the formula in AY3 down
Column
AY,
as far as there is data in Column AX.
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may
benefit!
==============================================
"George A. Yorks" .(donotspam) wrote in
message
...
Thanks for the help. I am confronted with a new scenario which
I'll
present
for help.
One worksheet titled geo I have a list of names b4 thru b18
and data in
k4thru k18. I want to transfere the data to worksheet titled
miriam
after
searching for the exact names. The names of the second
worksheet are
column
ax3 thru ax89. Hope this makes sense to you.
George
"RagDyer" wrote:
First question:
Replace error message with a null ( "" ), which can then be
added:
=IF(ISNA(MATCH(A1,'Sheet2'!$A$1:$A$10,0)),"",VLOOK UP(A1,
'Sheet2'!$A$1:$D$10, 4, 0)
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may
benefit!
==============================================
"George A. Yorks" .(donotspam) wrote in
message
...
I keep saying thanks, your help is outstanding. I would like
to ask
two
additional questions. ie: When I enter my formula into the
cells of
column
A
in a number of cells it returns a value in a few it
returns#N/A.
There
is
no data in those cases but with the #n?A when I try to add
the columns
will
not do so as it cant enter a non digit. I'm using 0 for the
last
number
in
my formula. How best to get around this.
Also I've copied a table from USA internet. In pasting to
worksheet
all
the
cells are obliterated and nothing is recognized by excel.
The data is
however recognized in (pardon me) lotus. Is there anyway of
having my
vlookup formula search 123 in place of a sheet in excel.
=vlookup(a1,
sheet2!$A$1:$j$31, 10,0). Hope these questions make sense to
someone
"Earl Kiosterud" wrote:
George,
The 4 is the third parameter of the VLOOKUP function, and
tells it to
retrieve the cell in the 4th column of the range being
looked up in.
Did
I
say being looked up in? Oh, well. :)
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
"George A. Yorks" .(donotspam) wrote in
message
...
Thanks for the help. I know I'll fully understand the
formula
structure
soon. One question, at end of formula ,4,0 what in fact
does the 4
make
reference to?
"Ragdyer" wrote:
Try this:
=VLOOKUP(A1, 'Sheet2'!$A$1:$D$10, 4, 0)
--
HTH,
RD
-------------------------------------------------------------------------
--
Please keep all correspondence within the NewsGroup, so
all may
benefit
!
-------------------------------------------------------------------------
--
"George A. Yorks" .(donotspam) wrote
in
message
...
Thanks for the help. One further question.
In the formul In sheet 2 $A$1:$b$10makes reference to
two
adjacent
columns.
If there is a column between so there is data in a and
c and
none
in
b
the
result return err,the formula in this cell referes to
cells that
are
currently emply. How to get around this. ie" if sheet
two has
data
in
column
a and column d.
thanks
George Yorks
"Earl Kiosterud" wrote:
George,
In B1 of sheet 1:
=VLOOKUP(A1, 'Sheet2'!$A$1:$B$10, 2, FALSE)
Copy down with fill handle to B10.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------
"George A. Yorks" .(donotspam)
wrote in
message
...
Trying to create a formula to do the following:
Sheet 1 column A a list of personal names a1-a10
Sheet 2 has list of names a1-a10 and list of
dollar amounts
colums
d1-d10
want to search sheet one and if any name from
sheet 2 found
on
sheet 1
than
the corresponding dollar amount is entered.
Any help appreciated.
--
George