Home » Category » Microsoft Excel

Microsoft Excel: ZERO, FIRST CHARACTER ON A NUMBER STRING

205| Thu, 22 May 2008 23:27:00 GMT| anonymous| Comments (3)
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

Keywords & Tags: zero, first, character, number, string, microsoft, excel

URL: http://www.developertags.com/microsoft-excel/495002/
 
«« Prev - Next »» 3 helpful answers below.
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
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
>.
>

mark | Thu, 22 May 2008 23:29:00 GMT |

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
--
Message posted from http://www.ExcelForum.com

mnewdick | Thu, 22 May 2008 23:30:00 GMT |

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

"WAYNE DOLLEY" <wayne.dolley...toshiba-ac.com> wrote in message
news:D17A5C10-FD49-4A45-8D06-FB58B7A82F22...microsoft.com...
: 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

lady | Thu, 22 May 2008 23:31:00 GMT |

Microsoft Excel Hot Answers

Microsoft Excel New questions

Microsoft Excel Related Categories