Dear Excel Forum,
Please find attached two excel sheets.
They have similar data in file Sheet 1 and file Sheet 2.
There are some spaces which I used Trim Formula to remove.
Checked the length of the text strings and found them exactly same.
Yet in Sheet 1 the Vlookup did not pick up the item codes from Sheet 2
Kindly Assist.
Thank You to all in Advance.
Subash S D
None of the strings in Sheet1 are an exact match for the strings in Sheet2. Sheet1 has character code 160 in several places, which look like blanks but they are not. For example
everything that looks like a space is actually character 160, which is a non-breaking space. It will not match a space (code 32).
Please Login or Register to view this content.
You just need to clean up your data.
This will work for some of the data, but not all of it.
Formula:
Please Login or Register to view this content.
There are still inconsistencies in both files. Leading or lack of spaces (char(32)) or non-breaking spaces (char(160)) muddy the waters and I have struggled to cater for all of the issues.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Thank You,
Can you guide, how to remove the character 160 and replace with simple space.
Thanks again in advance.
Subash S D
Find and Replace on the Ribbon.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thank You,
Yes, it did work for all in the list except the first one.
I shall try on main data now and see how it works.
Thanks once again please.
Thank You,Originally Posted by TMS
This will work for some of the data, but not all of it.
Formula:Please Login or Register to view this content.
There are still inconsistencies in both files. Leading or lack of spaces (char(32)) or non-breaking spaces (char(160)) muddy the waters and I have struggled to cater for all of the issues.
Yes, it did work for all in the list except the first one.
I shall try on main data now and see how it works.
Thanks once again please.
What a mess!! Poor you. try this, which works for all the examples in your sheet:
=INDEX([sheet2.xlsx]Sheet1!$B$4:$B$11,MATCH(TRIM(SUBSTITUTE(A3,CHAR(160)," ")),TRIM(SUBSTITUTE([sheet2.xlsx]Sheet1!$A$4:$A$11,CHAR(9),"")),0))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks