Good day, everyone. I’m a pharmacist with the Department of Veterans Affairs, and I frequently have to import text files created on our local network. One of the fields is the National Drug Code (NDC) number. Many of those numbers start with leading zeroes which have to be there to make sense. I have to import the field as Text, else all the zeroes disappear. Then I have to change the format to General, else the formulas don’t work. One attachment is a picture of what the data should look like. The formula works if the NDC # field is 12 characters long. Sometimes it isn’t. A few years ago I got some help to create a VBA function to format the NDC code. Here it is.
Option Explicit
Public Function NDC(ByVal X As Variant)
Dim m
If Right(X, 1) = ” ” Then
m = Left(X, 5) & “-” & Mid(X, 6, 4) & “-” & Mid(X, 10, 2)
ElseIf Len(X) = 12 Then
m = Left(X, 6) & “-” & Mid(X, 7, 4) & “-” & Right(X, 2)
ElseIf Len(X) = 11 Then
m = Left(X, 5) & “-” & Mid(X, 6, 4) & “-” & Right(X, 2)
ElseIf Len(X) = 10 Then
m = Left(X, 4) & “-” & Mid(X, 5, 4) & “-” & Right(X, 2)
Else
m = “error”
End If
NDC = m
End Function
When I try to insert the function now, I get an error message. A capture of that is attached also. I can’t figure out why the VBA function doesn’t work. Thanks for any help!
