help to correct text cells with different lengths

[donnl]

I'm trying to match names of companies in one worksheet with another but LEN returns different values. I use trim and still different lengths. What can I use so they match? My goal is to use vlookup or another function to see company rankings on other worksheets. Can anyone provide advisement?

 

[donnl]

Did you run it on both lists?

Let's go back to the beginning and ask the obvious question.
Are you sure that your two lists actually have matching values?
If the name of the Companies aren't exactly the same, it will not work (i.e. if there is a period at the end of one, but not the other).

Pick out an example that you think matches, and list what the value is, the LEN is, and what the last character code is (using the formula provided earlier), and post all that information here.

Thanks for the macro. It works great! I ran it on the worksheet -all lists and am getting same LEN or true values when comparing individual cell.

It is too much to compare to all values. These names were never entered uniformly. For example, one year it enters "company name" "co." another year the entry is "company name" "co.,inc.", LLC etc. vlookup works great now with this inaccuracy. Perhaps find/replace or substitute may work better. Thanks again.

 

[Joe4]

You are welcome. Yes, it is very tough to match data if it is not entered uniformly. Over the years, some people have done some pretty cool things with VBA called "Fuzzy Matching", which looks for "near matches". That might be helpful in your situation.

There is an old link (to other links) on that here: http://www.mrexcel.com/forum/hall-fame-winners/69649-alans-udfs-fuzzy-match-problem.html, and you can probably find others with a Google search.