Trouble removing formatting in vlookup area

[MSC]

Hi, I've used vlookups for many years but recently inherited a project involving a 3rd party data source, and a vlookup problem I'm not able to solve.

This spreadsheet has about 50 product names in column A, and corresponding data in columns B, C, D, etc., straight across in rows.
To the naked eye, the formatting doesn't appear to be odd, but it's strange alright: when I setup the vlookup in the destination tab, -most- of the formulas work, but not all.

After searching every which way to strip out funky formatting from the names in column A (=trim, text, paste into notepad, paste values, etc.), the only way I can get the problematic vlookups to work is to manually type over the product name. Sometimes the product name is 2 words, sometimes it's just 2, and hitting F2 reveals there are no stray spaces on the front or back.

What else could I do to help solve this without retyping every name? Thank you, love this forum....

 

[MSC]

Hi, I've used vlookups for many years but recently inherited a project involving a 3rd party data source, and a vlookup problem I'm not able to solve.

This spreadsheet has about 50 product names in column A, and corresponding data in columns B, C, D, etc., straight across in rows.
To the naked eye, the formatting doesn't appear to be odd, but it's strange alright: when I setup the vlookup in the destination tab, -most- of the formulas work, but not all.

After searching every which way to strip out funky formatting from the names in column A (=trim, text, paste into notepad, paste values, etc.), the only way I can get the problematic vlookups to work is to manually type over the product name. Sometimes the product name is 2 words, sometimes it's just 2, and hitting F2 reveals there are no stray spaces on the front or back.

What else could I do to help solve this without retyping every name? Thank you, love this forum....

I should add that I'm using another computer for this: Windows 10 v 22h2, Excel says "version 2409".

 

[Peter_SSs]

Excel says "version 2409".

Look higher up for the version. What does it say here?

It would also help if we had a small sample set of the problematic data with a few (failing) VLOOKUP formulas on the same sheet with XL2BB (generated from the Windows machine).
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[MSC]

Look higher up for the version. What does it say here?

View attachment 118859

It would also help if we had a small sample set of the problematic data with a few (failing) VLOOKUP formulas on the same sheet with XL2BB (generated from the Windows machine).
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Thank you for your reply. This is a work computer and the product info piece looks a little different from what you have above; see below.

As for the actual data itself, the info is unfortunately proprietary so I'm not able to share. I realize this may limit troubleshooting efforts.
Thank you for your help in brainstorming ways around this strange issue, Mike

 

[Peter_SSs]

product info piece looks a little different from what you have above; see below.

That is because what I showed and what I asked about was the Excel version, not the Windows version.

As for the actual data itself, the info is unfortunately proprietary so I'm not able to share.

Is it not possible to disguise or replace the information & still demonstrate the issue?

 

[MSC]

Here is my Excel version, do we think my version may have something to do with the issue?

In addition, I think I've figured it out:
As I was creating a version of my file to share with this thread, and attempting to mask some of the information, I decided to delete a few of the letters in each name so that the full names would not be revealed.

In so doing, I found several where I hit F2 to advance the cursor to the far right of the word, hit Backspace once, but the cursor didn't move. Telltale sign that there's an odd character in there, which I then copied and did Find/Replace: 37 instances of this. Is there a way to detect this other than the method I used?

2 others actually did have an extra space @ end of the word. I've fixed all this and everything now works.

 

[Peter_SSs]

Thanks. That shows that you are using version 365 not 2021 as shown in your profile. Might be a good idea to update your profile so that helpers know what functions, resources etc that you have available to you. If you are using 2021 on a Mac and 365 on Windows, you can show both versions and both platforms in your profile.
Also looks like you have some updates to install as well.

Glad you solved the problem of the extra spaces and unusual character. Thanks for letting us know what was happening.

Are you going to have to deal with data like this again? If so, we can probably find a way to deal with it without manually dealing with the extra spaces and other character.
If you want to follow up on that please post a small sample set of the 'dodgy' data with XL2BB
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)
If you use XL2BB I think that it will need to be with your Windows machine as I think the only Mac version that works with XL2BB is 365, not 2021.

 

[Alex Blakenburg]

I found several where I hit F2 to advance the cursor to the far right of the word, hit Backspace once, but the cursor didn't move. Telltale sign that there's an odd character in there,

What do you get if you enter the formula below replacing A1 with the Cell Reference that has the issue you can resolve using the backspace:

=UNICODE(RIGHT(A1,1))

Code 8203 will behave in the manner you describe.
Note: I am not sure that an XL2BB will help in this scenario since it tends to clean up codes such as 8203 and 160.

 

[MSC]

What do you get if you enter the formula below replacing A1 with the Cell Reference that has the issue you can resolve using the backspace:

=UNICODE(RIGHT(A1,1))

Code 8203 will behave in the manner you describe.
Note: I am not sure that an XL2BB will help in this scenario since it tends to clean up codes such as 8203 and 160.

Indeed it returns 8203......

 

[MARK858]

Try running the code below on a copy of your sheet

Sub Remove8203()
    ActiveSheet.UsedRange.Replace what:=ChrW(8203), Replacement:="", lookat:=xlPart
End Sub