Custom cell format
 

A1 thru A31 contains a total of 214 characters.

A lot of the cells have a custom number formats (like 00000000000 or
00000 or 0000000).

A32 contains =A1&B1&C1 thru A31

=len(A32) is 97 (not 214).

Is this discrepancy due to the custom number formats?

How do I get the result of =len(A32) to be 214?

Custom cell format
 

gary

LEN function returns only the number of characters in the cell.

Formatting to add leading zeros won't add characters to the cell.

So......A1:A31 does not contain 214 characters

I don't know of a workaround other than to do away with the Custom Formatting
and preface the cells with an apsotrophe and enter the numbers as '00000123


Gord Dibben MS Excel MVP

On Sun, 25 May 2008 09:58:00 -0700 (PDT), gary
wrote:

A1 thru A31 contains a total of 214 characters.

A lot of the cells have a custom number formats (like 00000000000 or
00000 or 0000000).

A32 contains =A1&B1&C1 thru A31

=len(A32) is 97 (not 214).

Is this discrepancy due to the custom number formats?

How do I get the result of =len(A32) to be 214?

Custom cell format
 

Hi

Using a custom number format to show leading zero's do not make the length
of the number to equal the ammount of zero's in the number format.

To get the desired result you have to put a single quotation sign in front
of the number ('00001), and enter the number including leading zero's in
cells A1:A3

Regards,
Per



"gary" skrev i meddelelsen
...
A1 thru A31 contains a total of 214 characters.

A lot of the cells have a custom number formats (like 00000000000 or
00000 or 0000000).

A32 contains =A1&B1&C1 thru A31

=len(A32) is 97 (not 214).

Is this discrepancy due to the custom number formats?

How do I get the result of =len(A32) to be 214?

Custom cell format
 

Since the number of digits varies, is there a way to automatically add
zeroes between the single quote and the first significant digit of the
number so the result has the correct number of characters?

Custom cell format
 

Why not use TEXT(A1,"000000"), for example?
--
David Biddulph

"gary" wrote in message
...
Since the number of digits varies, is there a way to automatically add
zeroes between the single quote and the first significant digit of the
number so the result has the correct number of characters?