Formula for Alpha-Numerical IDs

[jrbattle26]

Greetings. I am trying to write a formula that adds zeros to the beginning of an identifier, if its numbers only. If the identifier has letters, I want the formula to return the same value.

Essentially all the number-only identifiers should be 9 digits.

For example: CA12345 in column A should return CA12345 in column B. But if the identifier is 12345, I want it to add 0000 to the beginning, so column B would return 000012345.

Usually the identifiers have letters in the middle of the string of text, for example 1234ABC5.

I would like column C to display "Good" if its an alphanumerical ID, but show the number of 0s if its a numerical value less with less than 9 digits.

I've attached an example workbook for reference.

Thanks,

[jtakw]

Hi,

I don't know if Columns B and C are your "helper columns" and is actually needed, but you can just put this in D2 copied down:

Please Login or Register  to view this content.

[jrbattle26]

Thanks, works great. Additional columns would be helpful for user, but not necessary. Appreciate the help.