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?

 

[Joe4]

Welcome to the Board!

I'm trying to match names of companies in one worksheet with another but LEN returns different values.

If some of the data coming from an external source (i.e, Web, another program, etc)?
Many times, they will have special characters like non-breaking spaces, especially at the end.

So we need to identify that these characters are. Do that by doing the following:
1. Locate a value where the LEN function returns a number larger than you expect (like if "CAT" returns 4 instead of the expected 3)
2. Apply this formula to that cell, and let us know what it returns:
=CODE(RIGHT(A1,1))
(where A1 is the address of the cell that holds the value you are checking)

 

[donnl]

Welcome to the Board!


If some of the data coming from an external source (i.e, Web, another program, etc)?
Many times, they will have special characters like non-breaking spaces, especially at the end.

So we need to identify that these characters are. Do that by doing the following:
1. Locate a value where the LEN function returns a number larger than you expect (like if "CAT" returns 4 instead of the expected 3)
2. Apply this formula to that cell, and let us know what it returns:
=CODE(RIGHT(A1,1))
(where A1 is the address of the cell that holds the value you are checking)

Yes. This was downloaded from a website. I used the code provided and received a value of 32 which was lower than a trimmed value from another worksheet.

 

[Joe4]

32 is just a regular space (ASCII Code - The extended ASCII table), which the TRIM function should handle without any issues.
How exactly are you using the TRIM function in your formula?
Is it the range you are looking up or the range you are looking into in your formula?

If there shouldn't be any spaces at all in your cells, you can simply replace all spaces with nothing, using Find/Replace to fix that issue.

 

[donnl]

I use =TRIM(B11) or on another sheet, =TRIM(B5).
On one wksht: LEN =38, then trim and LEN = 37 and using CODE(RIGHT) = 32
On the other wksht: LEN =34, then trim and LEN=34 and using CODE(RIGHT)=46

 

[Joe4]

OK, in a VLOOKUP function, you have 4 arguments.
The first is the value you are looking up.
The second is the range you are looking in and matching on.

Which of these two has the values with the extra spaces at the end?

Are there any valid spaces in any of these cells that you want/need to keep?
If not, can you apply the last suggestion I provided in the previous post?

 

[donnl]

Which of these two has the values with the extra spaces at the end?
In this case, the first value is the one with the code(right) = 46 and the second value is a company name contained within a list which has a code(right) =32.

Are there any valid spaces in any of these cells that you want/need to keep?
I don't need the whole company name. If I could pare down the name to the first three or four words I think it would allow me to use vlookup. I'm just not sure about leading spaces. Could you please advise?


If not, can you apply the last suggestion I provided in the previous post?
Both lists reverse roles of lookup value and table array. I want to see how they rank within both lists and display their rank in a separate column. The lists are long, one is 100 and the other is 400 and don't know if find /replace is efficient unless I have no other choice.

 

[Joe4]

ASCII code 46 is just a period, so it doesn't appear that there are any extra spaces at the end of that one.

I have come up with a little macro for you that will eliminate all extra trailing spaces at the beginning and end of your entries. Just highlight the range you want to apply it to and run this macro.

Sub TrimSpaces()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Selection
        cell = Trim(cell)
    Next cell
    Application.ScreenUpdating = True
End Sub

 

[donnl]

ASCII code 46 is just a period, so it doesn't appear that there are any extra spaces at the end of that one.

I have come up with a little macro for you that will eliminate all extra trailing spaces at the beginning and end of your entries. Just highlight the range you want to apply it to and run this macro.

Sub TrimSpaces()
    Dim cell As Range
    Application.ScreenUpdating = False
    For Each cell In Selection
        cell = Trim(cell)
    Next cell
    Application.ScreenUpdating = True
End Sub

Thanks for the macro. I ran it but still get different values with the LEN function.

 

[Joe4]

Thanks for the macro. I ran it but still get different values with the LEN function.

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.