I am not sure if this question is labeled correctly, but I am trying to import data from an Excel document into FileMaker. I want to have a 3 character/digit code for a color code. Some color codes are 3 letters, or a combination of alpha and numberic numbers, but many documents that we get have a color code for Black listed as "2" (as the person putting together the document does not know how to enter the '002 in Excel (and even if they did FileMaker only sees the "2", even in a text formatted field) and not the "002" that it needs to be. Or "44", not the "044" that it needs to be.
Is there a way to automate the validation/entry? The Char (number) function only counts actual alpha characters and does not specify numeric characters.
As an example, I want to:
entered Color Code: "KX7" leave as KX7
entered Color Code: "44" change to 044
entered Color Code: "2" change to 002