You can get rid of leading/trailing/duplicate internal spaces by using a helper
column with a formula like:

=trim(a1)
copy down the column.

Then you can copy|paste special|values right over the original list and delete
the helper column. (Do this on the lookup table, too.)

If you copied from a web page, maybe you're seeing the non-breaking HTML spaces.

David McRitchie has a routine that will clean up this kind of stuff at:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")



George A. Yorks wrote:
>
> My column is comprised of names with varied spaces between first and last
> name.As you suggested went to edit find/replace and in the find place the
> name with extra spaces. in the replace entered the name with one place and
> clicked on find. Nothing. The help menu is of no help. Has to be
> something obvious that I am not doing
>
> "Dave Peterson" wrote:
>
> > Look under Edit|Replace on the worksheet toolbar.
> >
> > 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
> > > > <gyorks@comcast.net.(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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > >> > news:2B948C21-AD25-4713-A551-A6ED3965591B@microsoft.com...
> > > > >> > 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)),"",VLOOKUP(A1,
> > > > >> > > 'Sheet2'!$A$1:$D$10, 4, 0)
> > > > >> > > --
> > > > >> > >
> > > > >> > > HTH,
> > > > >> > >
> > > > >> > > RD
> > > > >> > > ==============================================
> > > > >> > > Please keep all correspondence within the Group, so all may benefit!
> > > > >> > > ==============================================
> > > > >> > >
> > > > >> > >
> > > > >> > > "George A. Yorks" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > >> > > news:D717F2F0-DDE4-485A-9909-E3CFF61BC1F9@microsoft.com...
> > > > >> > > 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" <gyorks@comcast.net.(donotspam)> wrote in message
> > > > >> > > > news:8F924F5C-856D-48A0-9D3C-9BF538748E3D@microsoft.com...
> > > > >> > > > > 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" <gyorks@comcast.net.(donotspam)> wrote in
> > > > message
> > > > >> > > > >> news:85A106A1-3EAC-45B1-81F1-E47139806E7B@microsoft.com...
> > > > >> > > > >> > 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" <gyorks@comcast.net.(donotspam)> wrote in
> > > > >> > message
> > > > >> > > > >> > > news:316947CC-1088-49F7-AEE2-3E81DE914D9D@microsoft.com...
> > > > >> > > > >> > > > 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
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >> > >
> > > > >> > > > >>
> > > > >> > > > >>
> > > > >> > > >
> > > > >> > > >
> > > > >> > > >
> > > > >> > >
> > > > >> > >
> > > > >> >
> > > > >> >
> > > > >> >
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson