Power Query Problems

How to Format NDC Numbers in Excel Power Query

Maira Mehtab Sep 21, 2026 869 views

Question details

The user needs to convert numeric National Drug Code (NDC) data into a specific text format (00000-0000-00) to ensure leading zeros and hyphens are properly displayed.

Product: Excel Power Query
Device & OS: not provided
Scenario: Formatting raw numerical data into standardized NDC strings during the data transformation process in Power Query.
Observed behavior: The NDC values are stored as raw numbers, causing them to drop leading zeros and lack the necessary hyphens required for the final 11-digit format.

Before you start

Ensure your dataset is loaded into the Power Query Editor and identify the exact name of the column containing your numeric NDC values before applying the custom format.

Solution 1Recommended

Use Number.ToText in a Custom Column

Create a custom column in Power Query using the Number.ToText function to force the numeric value into an 11-digit text string with hyphens.

Because Power Query treats raw numeric columns by stripping leading zeros, converting the number to text with a designated format mask is the most reliable way to enforce the standard NDC format.

Open the Add Column Tab

In the Power Query Editor, navigate to the 'Add Column' tab on the top ribbon.

Insert a Custom Column

Click on 'Custom Column' to open the Custom Column dialog box.

Apply the M-Code Expression

Name your new column (e.g., 'Formatted NDC') and enter the following formula in the Custom column formula box: =Number.ToText([YourColumnName], "00000-0000-00"). Be sure to replace '[YourColumnName]' with the actual name of your NDC column.

Save and Apply

Click 'OK' to create the column. The output will now display as text, preserving all leading zeros and applying the specified hyphenated pattern.

Case Sensitivity: Power Query (M formula language) is case-sensitive. Make sure to type 'Number.ToText' exactly as shown, with capital N and T.

Free Microsoft Office alternative

Need a lightweight and efficient alternative to Microsoft Excel?

If you often deal with complex data formatting and find Microsoft Office too heavy, expensive, or complex, try WPS Office. It provides powerful data processing tools, native spreadsheet text formatting, and is completely free to download.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install WPS Office: Run the setup file and follow the quick on-screen instructions to install the suite.
3. Open Your Spreadsheets: Launch WPS Spreadsheet and open your existing Excel files to easily format and manipulate your data.

Free and lightweight Office suite with zero subscription feesFully compatible with Microsoft Excel (.xlsx, .xls) and CSV formatsEasy-to-use cell formatting tools to handle leading zeros and custom text stringsFamiliar tabbed interface requiring zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

Why do my NDC numbers lose leading zeros in Excel and Power Query?

When numbers are stored in a standard numeric data type, spreadsheet programs automatically remove leading zeros because they hold no mathematical value. To preserve them, the data must be specifically formatted or converted to a text data type.

Can I format NDC numbers directly in standard Excel without Power Query?

Yes. You can use standard Excel Custom Formatting by selecting your cells, pressing Ctrl+1 to open Format Cells, choosing 'Custom', and typing '00000-0000-00'. Alternatively, you can use the formula =TEXT(A2, "00000-0000-00").

What does the Number.ToText function do?

Number.ToText is a Power Query M function that converts a numeric value into a text string. By adding a format string like "00000-0000-00" as the second argument, you can instruct it to pad the number with zeros and insert specific characters like hyphens.

Excel & Data Problems