Excel not recognising the data, while format is same

[Timeless5]

Hi,

The data in the attached file in 2 columns are from 2 different files. When I failed to pick the data by vlookup, then I pasted samples of both for help.

Why does Excel not recognise the data, while the format of both the cells is "General"? If I try "Search", then with the data in the first row, then it doesn't find the data in 2nd row. The sheet is attached HERE.

 

[etaf]

the first column has leading and training spaces , hence why it does not work
A1 has a leading space
A2 has a trailing space

you could use trim(A1) in column B , copy down to remove any spaces

you are showing 2007 version, i assume as its your first post - that is the version you are using

 

[Fuji]

This code removes leading/trailing space(s) from Col.A

Sub test()
    With Intersect(ActiveSheet.UsedRange, Columns(1))
        .Value = Application.Trim(.Value)
    End With
End Sub

 

[Timeless5]

Thanks a lot, dear @etaf.
It's strange that if someone mistakenly adds space like this in the middle of long sheets, then Excel formulas won't work unless Cleaned.

 

[etaf]

well, if you did want spaces, you would be equally hacked off if excel returned values regardless of leading or training spaces
you could use wildcard for the lookup - but that would be equally as bad i suspect
=vlookup( "*"&cell&"*" ,
if you wanted
123
and had in the range
1234
12356
1235
123

it would find the first one of those , so not the one with just 123 in

as i say , is it excel 2007 , later version may be possible with a formula - not sure

 

[Timeless5]

Yes, my version is Excel 2007.