Help Formatting Numbers

[pcinch]

I have a group of census tract numbers that I need to normalize in order to analyze.

The general format of a census tract number is ####.## but not every tract has six digits. I will need to remove the decimals, and add leading and trailing zeroes to get each of the numbers to be in the form of ######

For example, census tract 141.29 needs to become 014129, and tract 6 will become 000600.

Any help would be much appreciated as I am new to excel

 

[bbott]

Welcome to the board.

Are any of your whole number values more than 4 digits (e.g. 99999)?

 

[pcinch]

Nope

 

[Joe4]

Welcome to the Board!

I don't think you can use any sort of Custom Formatting to do that (see:How to multiply in custom format - Microsoft Community).

But, you can use a formula like this to return what you want:
=TEXT(A1*100,"000000")

 

[pcinch]

That worked! Thanks for all your help!

Can i mark the thread as solved somehow?

 

[Joe4]

You're welcome!

Can i mark the thread as solved somehow?

You just did!
Your acknowledgement is sufficient.

 

[Rick Rothstein]

I don't think you can use any sort of Custom Formatting to do that

Given that the OP's numbers are not going to be used in any mathematical calculations, there is a Custom Format that will visually present the numbers the way the OP wants... select the cells to format and open the Format Cells dialog box, then on the Number Tab, select Custom and keyin 000000 then press CTRL+J and then keyin a percent sign (%) into the Type field, then go to the Alignment Tab and put a check mark in the "Wrap text" checkbox, then click the OK button... the display will contain the 6 digit number the OP wants and the percent sign that is attached to it will be invisible below the bottom of the cell.