ZERO, FIRST CHARACTER ON A NUMBER STRING

W

WAYNE DOLLEY

I HAVE A VALIDATION WHERE I HAVE EIGHT DIGIT SERIAL NUMBERS WHICH CAN INCLUDE A CHARACTER.
Eg. 14380001 , 2345C029.

Setting the validation to text length equal to "8" This works fine.

The problem I encounter is that some serial numbers start with 0.
Eg. 04189532.
This is not accepted using the above validation. can anyone help.

Many Thanks.
Wayne
 
M

Mark

If the serial numbers don't have to be used in any
mathematical calculations, then make sure they are
formatted as text or entered as text. Doing this will
mean, for example, that the number 02 will be treated as
two characters. If it's entered as a number, it will be
converted to the number 2 (1 character).
-----Original Message-----
I HAVE A VALIDATION WHERE I HAVE EIGHT DIGIT SERIAL
Click to expand...
NUMBERS WHICH CAN INCLUDE A CHARACTER.
 
M

mnewdick

Having the users enter any length character string and have Exce
preload any required leading zeroes is, I have found, impossible.
However, the solution I use is this:

In an adjacent (perhaps hidden) column, set up a formula that read
thus:

=if(len(A1)<8,vlookup(len(A1),TABLE1,2,0)&A1,A1)

With TABLE1 looking like this:

0 00000000
1 0000000
2 000000
3 00000
4 0000
5 000
6 00
7 0
8

(In two columns, obviously.)

You can do a further "IF" to qualify blank cells, if necessary.

Hope that helps!

Mar
 
L

Lady Layla

SEt cells to text or create a custom number format of 00000000

: I HAVE A VALIDATION WHERE I HAVE EIGHT DIGIT SERIAL NUMBERS WHICH CAN INCLUDE
A CHARACTER.
: Eg. 14380001 , 2345C029.
:
: Setting the validation to text length equal to "8" This works fine.
:
: The problem I encounter is that some serial numbers start with 0.
: Eg. 04189532.
: This is not accepted using the above validation. can anyone help.
:
: Many Thanks.
: Wayne
 
Top