Custom cell format

[gary]

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?

 

[Gord Dibben]

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

 

[Per Jessen]

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]

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?