imported text not recognised in vlookup

marleyps

New Member
Joined
Aug 29, 2003
Messages
38
Hello
I have a problem whereby I have to copy data from my work system into excel and then run a vlookup againt the data.
The problem is that the data is not recognised by the vlookup formula in all cases. the data is part numbers, and is a mix of numerical and alpha numerical.
eg

30524
30536
30568
30569
30584
H1003/45
H1101/44/RE
H1172/40/RE

i run the following code against each line
=VLOOKUP(A1,[PERSONAL.XLSB]KANBAN!$A$1:$B$350,2,FALSE)
and the result should be "Kanban" if there is a match to the lookup table. sometimes the code works but in alot of cases is does not recognise the data . If I overtype the part number and there is a match the code works. I have tried the trim command to remove any leading or trailing spaces, to no avail I am at a loss to understand why the data is not recognised. Any help would be appreciated.

Phill
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
If this data comes from the web it could contain CHAR(160) which I dont think TRIM() removes.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top