ZERO, FIRST CHARACTER ON A NUMBER STRING

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
Back
Top