Vlookup function

  • Thread starter Thread starter cathlyn
  • Start date Start date
C

cathlyn

Hi,
I have a prob. when using vlookup. I have 2 worksheets,
and I need to look up for values in the 1st sheet, to the
2nd. By right, I need common datas in both worksheets,
but my common data is not in proper format. The common
data which I have here is location, and the data are in
this format: L1, L2, etc. however, the character spacing
in betweens are not consistent. Some are as L 1,L2, L3 ,
etc. I am unable to use vlookup in this case. Is there
any solution?
 
Hi!

Why not use EDIT/REPLACE and get rid of all the spaces?

Select the range where the L 1, L3 , L 4 , are.
Goto EDIT>FIND
Find what: hit the space bar
Click the REPLACE tab
Replace with: nothing, leave this blank
Click the REPLACE ALL button
Click CLOSE

Now all the spaces should have been removed and you can do
your lookups on L1, L2 etc.

Biff
 
Hi all,

Thanks for your suggestions. However, I have a long list
of data for reporting. So, if I use the substitute or
replace function, I don't think I will be able to finish
all my reports in time. Any other ways to solve this?
Thanks.
Cathlyn
 
Hi!

Both suggestions were very good and should only take a
matter of seconds to perform. I don't understand not
having enough time?

I can think of at least one more way to do this but it's a
convoluted hack that will require more time and is not as
robust as the solutions already offered.

Biff
 
Hi

When spaces in LookupValue aren't substantial, and when removing them you
are done with it forever, then removing them with single stroke is really
the best way.

Select the range with all location data, from menu select Replace, into
FindWhat field enter a single space, leave ReplaceWith field empty, and
press ReplaceAll button - all spaces in selected range are removed. NB! When
you don't select any range (you select a single cell), all spaces on whole
worksheet are removed!
 
Back
Top