How to Format NDC Values as 5-4-2 with Leading Zeros in Excel
Question details
The user needs to normalize National Drug Code (NDC) values containing hyphens into a strict 5-4-2 format by dynamically adding missing leading zeros to the segments.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Standardizing pharmaceutical data where NDC segments have inconsistent lengths and are missing necessary leading zeros.
- Observed behavior
- Current NDC entries have inconsistent lengths across the three hyphen-separated groups, and standard number formatting removes required leading zeros.
Ensure that the destination column where you will output the formatted NDC values is formatted as 'Text' so Excel does not automatically remove the newly added leading zeros.
Use Text and String Functions to Format NDC Values
Apply a combination of TEXT, LEFT, FIND, and REPLACE functions to extract each hyphenated segment and pad it with the correct number of leading zeros.
This formula splits the NDC value into its three component parts based on the hyphen delimiter. It then uses the TEXT function to enforce a 5-digit format for the first part, a 4-digit format for the second part, and a 2-digit format for the final part.
Right-click the column letter next to your NDC data and select 'Insert' to create a blank column for the formatted output.
Assuming your first unformatted NDC value is in cell A2, select cell B2 and enter the following formula: =TEXT(--LEFT(A2,FIND("-",A2)-1),"00000")&"-"&TEXT(--LEFT(REPLACE(A2,1,FIND("-",A2),""),FIND("-",REPLACE(A2,1,FIND("-",A2),""))-1),"0000")&"-"&TEXT(--TRIM(RIGHT(SUBSTITUTE(A2,"-",REPT(" ",LEN(A2))),LEN(A2))),"00")
Press Enter to calculate the result. Then, click the small square at the bottom-right corner of cell B2 (the fill handle) and drag it down to apply the formula to the remaining rows.
Use a VBA Macro to Normalize NDC Formats
Implement a VBA macro to automatically split the values at the hyphens, validate the length, and format each group appropriately.
Normalize and Format Medical Data Perfectly with WPS Spreadsheet
WPS Spreadsheet provides robust support for all advanced text functions and VBA macros needed to standardize complex data strings like NDCs, entirely for free.
- 1. Open your data file: Launch WPS Spreadsheet and open the workbook containing your NDC data.
- 2. Apply the text formula: Select the adjacent empty cell and paste the provided TEXT and FIND formula.
- 3. Drag to fill: Use the fill handle to drag the formula down, instantly formatting all NDC codes.
- 4. Paste as values: Copy the formatted column, right-click, and select 'Paste as Values' to remove the formulas and keep only the formatted text.
Frequently Asked Questions
Why does Excel keep dropping the leading zeros from my NDC codes?
By default, Excel treats numbers with leading zeros as standard integers and automatically removes the zeros. When data includes hyphens, Excel may interpret it as a date or a mathematical operation. Formatting the cells as Text or using the TEXT function forces Excel to display the exact characters.
Can I use custom cell formatting instead of a formula for the 5-4-2 layout?
Custom formatting like '00000-0000-00' only works correctly on pure numeric strings without existing hyphens. Because your source data already contains hyphens in arbitrary positions, custom formatting will not align the segments properly. You must use string manipulation formulas to process them.
Will the formula work if a segment of the NDC is completely missing?
The provided formula assumes that all three segments exist and are separated by two hyphens. If a segment is entirely missing, the FIND function will return an error. You would need to wrap the formula in an IFERROR function or use VBA to handle irregular segment counts.
