Vlookup - spaces

[cubby1]

Hi,
I am using vlookup and trying to match columns that has over 2000 rows, because the source data come from a mainfraim the values have spaces after the name which is causing it not to find exact lookups. How can I remove the spaces wihtout going into each cell and delete the spaces at the end?

 

[Dan E]

Cubby1,

Insert a blank column next to yours (say yours is A1),
in the blank column put =TRIM(A1) and drag down.
Copy and Paste Special as Values over your old data.

Dan E

Hi,
I am using vlookup and trying to match columns that has over 2000 rows, because the source data come from a mainfraim the values

have spaces after the name which is causing it not to find exact lookups. How can I remove the spaces wihtout going into each cell
and delete the spaces at the end?

 

[Jeane]

There are a couple things. To take spaces out of data,
for instance a name - Bob Jones Jr - use edit/replace.
In "Find What" put a space, in "Replace with" put nothing.
The result will be BobJonesJr. If the spaces are at the
end or beginning, use the Trim function. For instance, if
the data ( Bob Jones Jr ) is in column A, put in a new
column B. Then in B1, type =Trim(A1). The result in B1
will be Bob Jones Jr. The Trim function will remove
leading and following spaces, but leave spaces in the
data. Copy the edited data in Column B and do a Paste
Special/Value.

-----Original Message-----
Hi,
I am using vlookup and trying to match columns that has

over 2000 rows, because the source data come from a
mainfraim the values have spaces after the name which is
causing it not to find exact lookups. How can I remove
the spaces wihtout going into each cell and delete the
spaces at the end?

 

[Jeff H]

Use the Ctrl H function.
Search for a space and do not put anything in the replace field. Click on replace all

 

[Jeane]

There are two different methods to choose from. If you
want to remove all spaces, used Edit/Replace. In "Find
What" put a space, in "Replace With" put nothing. For
example, Bob Jones Jr will change to BobJonesJr. If you
want to remove leading or following spaces, but leave the
spaces in the date, use the Trim function. For instance,
if the data ( Bob Jones Jr ) is in A1, create a new
column B and type in B1 =Trim(A1). The result in B1 will
be (Bob Jones Jr). Do a Copy/PasteSpecial/Value on B1 to
make it permanent.

-----Original Message-----
Hi,
I am using vlookup and trying to match columns that has

over 2000 rows, because the source data come from a
mainfraim the values have spaces after the name which is
causing it not to find exact lookups. How can I remove
the spaces wihtout going into each cell and delete the
spaces at the end?

 

[Ken Wright]

Two ways:-

First, simply deal with the garbage spaces in the VLOOKUP function, eg:-

=--VLOOKUP(A1,TRIM(A5:E15),3,0) array entered using CTRL+SHIFT+ENTER, will give
you what you need.

OR, (and this I prefer):-

Cleanse the data prior to using it, and it's far simpler than it sounds. Simply
put a copy of Dave McRitchie's 'TrimAll' macro in your personal.xls, and then
run it against your spreadsheet. It's a one time hit that will take all of a
second or two, and you are done:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

I wouldn't be without this little gem

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

Hi,
I am using vlookup and trying to match columns that has over 2000 rows,

because the source data come from a mainfraim the values have spaces after the
name which is causing it not to find exact lookups. How can I remove the spaces
wihtout going into each cell and delete the spaces at the end?