I need to create a IF statement where it will count the character length and if not 6 then add leading zeros to field to make a fixed 6 character length .. so if field is 489 then the if statment would make it 000489

Hi,

are your values numbers or text? If they are numbers, you could apply a custom format of

000000

which would format any number with as many leading zeros as required to make a 6 digit number. This would still be a number that you can use for calculations.

The following formula will take numbers or text and add leading zeros. The result is text.

=REPT("0",6-LEN(A1))&A1

Exchange A1 for the cell where your original value is.

cheers

Originally Posted by knita
I need to create a IF statement where it will count the character length and if not 6 then add leading zeros to field to make a fixed 6 character length .. so if field is 489 then the if statment would make it 000489
Don't need to use IF statement. Format the cell.

Right click on the cell, hit FORMAT CELL. Choose NUMBER, then CUSTOM.

Under "Type" enter 00000# (five leading zeroes and a number sign)

That's it. The cell will always maintain the fixed character length.

Is that what you needed?

Yes - this solved my problem I can use either solution
THANKS !

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1