SSN's in different formats for lookup

[GMKoehler]

Hello. I have a file that was pulled from a system where the social security number came across as "general" with all the leading zeros and dashes. i.e. 005-12-3456. I have another file that has data that I want to pull into the first file but the SSN on that one came across as a "number" in the following format: 5123456. How do I get these two numbers to talk to each other in a lookup table? I have tried changing the format on both to Special--Social Security Number. I still get #N/A as the result. I checked for trailing spaces and there are none; but if I retype the social security number in the lookup table it does pull the data correctly. What else can I try? Thanks.

[Craig K.]

This is a little tough since we tend to ask for a sample file with sensitive data removed. Can't ask for that as your file is only sensitive data.

System pulls sometime inject extra spaces or other funky characters into strings without them being readily visible. Have you tried running every value through TRIM(CLEAN( )) then pasting special the values before running your lookups?

[shg]

You could use a convenient columns to convert the numeric values to the same format:

=TEXT(A1, "000-00-0000")

Copy, paste as values, and delete the original column.

[GMKoehler]

This is a little tough since we tend to ask for a sample file with sensitive data removed. Can't ask for that as your file is only sensitive data.

System pulls sometime inject extra spaces or other funky characters into strings without them being readily visible. Have you tried running every value through TRIM(CLEAN( )) then pasting special the values before running your lookups?

This did not work. It just took the cell formatted as Special--Social Security Number back to the number format without the leading zeros that I started out with. Errrgh. Thanks for helping, though.

[GMKoehler]

Ahh, this worked. I had to change the original SSN and the one in the lookup table using the =text(a1,"000-00-0000") formula and it works! Thanks you!!!

[GMKoehler]

This is a little tough since we tend to ask for a sample file with sensitive data removed. Can't ask for that as your file is only sensitive data.

System pulls sometime inject extra spaces or other funky characters into strings without them being readily visible. Have you tried running every value through TRIM(CLEAN( )) then pasting special the values before running your lookups?

Actually, after I converted the SSN to text using the =text(cell,"000-00-0000"), I did discover I had trailing spaces as well. Then I still had to do a =TRIM(SUBSTITUTE(D4,CHAR(160)," ")) to get rid of the spaces. Finally after all that the lookup worked! Thanks to both of you.