Persistent formatting issue (custom, special)

[Tacos_Tacos_Tacos]

Hello all, and thanks for reading.

I need to import a new spreadsheet (or simply its data) into an ongoing Excel project that I've been working on. The only identifying data on this new document are social security numbers (SSNs). They are hard-coded with hyphens, which I text-to-columns'ed, concatenated, and removed.

Now, throughout my existing project, there are also SSNs (without hyphens - just a nine-digit number). In order to preserve any leading zeros therein, they were formatted as a "custom" format ("000-00-0000"), which has worked just fine throughout the many lookups to which they are tied. And there are many: any number of them are linked to VLOOKUP, MATCH, and INDEX functions - the point being, I'm not in a position to change them in any way that's going to affect how/if they are used as references.

Here's the issue: on the existing project, that custom "000-00-0000" format seems to be automatically recognized as an SSN, and the relevant cells are automatically switched to a "special" format that indicates that it is, in fact, an SSN. This "special" format includes the hyphens that I originally wanted, so everything seems to be on the up and up.

However, the exact same thing happens with the SSNs from the new document, except that despite inputting my typical "000-00-0000" custom format, the SSNs are being switched to the same "special" SSN format, but without the hyphenation.

I have tried everything, and am at my wit's end. I have tried bringing the whole sheet into the new project. I have tried simply copying the SSN values from the new document into the existing document, with the target cells already formatted as I want. But these ~120,000 SSNs refuse to be formatted with hyphens, and I think it is fundamentally preventing them from being used as reference for lookups. I can find absolutely no information regarding this issue online, and it is a bizarre one.

To be clear, if one goes to the "special" format menu in the original project, it will show the nine-digit "SSN" format with hyphens. If one goes to the "special" format menu in the new document, it will show the nine-digit "SSN" format without hyphens, and that format seems to follow the values wherever they go, regardless of how they're moved. No amount of custom formatting will convince these formats to behave differently. And it appears that while the new document is insisting on formatting the numbers in the manner it has chosen, I am unable to use them as a reference.

I apologize if this is somewhat confusing. I feel the same way. Anyone have any insight?

 

[Rick Rothstein]

My guess is that the numbers that won't format are actually Text values, probably your number with a leading or trailing blank space. You might try this... select the column with the "numbers that won't format", make sure the cell format for them is "General", then call up the "Text to Columns" dialog box and as soon as it appears, click the Finish button. Now try your custom format on them.

Hopefully the above will work for you, but I am curious... if your SSNs looked correct when you first put them into the worksheet, why didn't you just leave them as they were? It seems your troubles started when you converted them to real number (by removing the dashes) and then tried to format them so they looked like they did when they were text values. Surely you are not doing an calculations with the SSNs after the were converted to be real numbers... so why did you need to make them real numbers at all?

 

[Tacos_Tacos_Tacos]

Your solution worked. Thank you very much. I had TRIMmed them and made sure there were no extra spaces anywhere, but it did not occur to me that the format that they may currently be in would affect the format to which one wants to change them.

When I first received the new SSNs, they had the hyphens included within each cell value. The problem is that none of the SSNs in the existing project actually had hyphens within their value; they were only formatted to appear as though they did. Thus, I was unable to perform lookups between the new and old SSNs, because the new ones actually had hyphens. I would have happily kept the new SSNs as text if all of the project's other SSNs weren't formatted differently.

Again, thank you for the assistance.

 

[Rick Rothstein]

Your solution worked. Thank you very much. I had TRIMmed them and made sure there were no extra spaces anywhere, but it did not occur to me that the format that they may currently be in would affect the format to which one wants to change them.

When I first received the new SSNs, they had the hyphens included within each cell value. The problem is that the none of the SSNs in the existing project actually had hyphens within their value; they were only formatted to appear as though they did. Thus, I was unable to perform lookups between the new and old SSNs, because the new ones actually had hyphens. I would have happily kept the new SSNs as text if all of the project's other SSNs weren't formatted differently.

Depending on which way you needed to go with your lookups, you could either have removed the hyphens from your new SSNs within the formula directly...

SUBSTITUTE(C3,"-","")

or introduced the hyphens into the formula for the numbers without them...

TEXT(D4,"000-00-0000")

Or, if your numbers coming in from now on out would always have hyphens, you might have considered introducing the hyphens into your existing numbers so that everything was in the same format from here on out.

 

[Tacos_Tacos_Tacos]

That's useful information. Thank you.