Log In
Register
Home
Dashboards
Power Pivot
June 25, 2016
Offline
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.
See Answer
July 16, 2010
Offline
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
Trusted Members
December 20, 2019
Offline
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
Dashboards
Power Pivot
June 25, 2016
Offline
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. 🙂
July 16, 2010
Offline
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
Answers Post
1 Guest(s)
