Excel Custom Number Format
Posted by Dan Wright on February 01, 2002 7:31 PM
I need to know how to set up a mixed (numbers & text) custom number format where the text could be in any position within the format.
Example:
###-##-#####-#
In each # above I may need to enter letters or numbers and need dashes in between each set. I do not know visual basic or scripting so I need an example if at all possible. I know that the (#) number sign indicates a number is to be entered, but I need a format that allows either letter or number in any position within the format.
Thanks,
Dan
Posted by Yogi Anand on February 02, 2002 2:07 PM
Hi Dan:
I am not aware of any formatting option even within custom number formatting that will allow alpha entries as you desire. However, you can set up an array wherein you check the validity of each entry in your string ... and then if all the entries in the array are true, you have a valid entry. So, based on the above approach, I set up a validation rule to verify that the entries I had made in my string met the validation rule. Here is to check that the first entry in your string is either a numeric or an upper case alpha character:
=OR(AND(CODE(MID(CELL("contents"),G48,1))>48,CODE(MID(CELL("contents"),G48,1))<58),AND(CODE(MID(CELL("contents"),G48,1))>64,CODE(MID(CELL("contents"),G48,1))<91))
and this is done for each of the entries, as noted above.
It is not an elegant solution but it works. Let us see, somebody else may have a better solution, otherwise if you are interested in this solution, I have it worked out, and I can email it to you on your asking.
Yogi Anand
ANAND Enterprises (broken link)
