I have a file where the SSN is missing the dashes and the leading zeros. If I create a new column with power query, what is the correct formula to format the numbers to the 000-00-0000 format?
Examples:
I need 1234567 to become 001-23-4567
12345678 to become 012-34-5678
123476789 to become 123-45-6789
Thank you for your assistance.
Hi Pam,
Power Query is not the place for formatting. This should be done once you load the data to Excel. You can use a custom number format:
000-00-0000
Hope that clarifies things.
Mynda
If you need the formula in excel to make it text you can use
=LEFT(REPT("0",9-LEN(B2))&B2,3)&"-"&MID(B2,LEN(B2)-5,2)&"-"&RIGHT(B2,4)
Purfleet
Hi Mynda and Purfleet,
Thank you for your responses. The SSN is the primary key between two files I am joining through Power Query. (One is already formatted, the other is not.) I was hoping I could reformat this within the query so that the person using the tool wouldn't have to do any formatting themselves.
So, is it just not possible in Power Query? I attempted something similar to what Purfleet posted, but it didn't work. I know the Power Query language isn't the same as a regular Excel formula.
Again, thank you for your help. 🙂
Hi Pam,
If you want the field to remain a number, then you can't format it in Power Query. If you're happy to convert it to text then you can use Pad.Start to get the correct number of characters, then split the text and insert the hyphens. See file attached.
Mynda
Thank you Mynda!!! That is perfect!
I pray all is well with you and yours in your part of the world!
