Formatting numbers in excel – few tips
Reader Nikhil Shah asks in an email:
Dear Chandoo,
I have some Error In Excel sheet.
Problem :
I have a locker number 01234 567890, in Excel it will be displayed as 01234 567890 with the space. However if it was entered as 01234567890, Excel will display it as 1234567890, without the leading zero. If the data has been read in from a text fill in could be formatted as text and still show 01234567890.
Now I want my locker number see with zero in excel sheet,How it possible,please help me with example.
You can force excel to format numbers the way you want using “format cell” feature. Just select the cell with number you want to format and hit ctrl+1 (or right mouse click > format cells) and go to Number tab in the dialog box. Select “custom” category and enter format as 000000 00000 (6 zeros followed by a space and then five more zeros) as shown below. Hit ok to set the format to display locker number as you desire.
Also, try these things:
 To set social security number (SSN) format, enter 000000000
 To set phone number format, enter 0000000000
 To set phone number with 1 digit country code format, enter (+0) 000 000 0000, for 2 digit country codes you can try (+00) 000 000 0000.
 To set social security number (SSN) format, enter 000000000
 To set ZIP code format, enter 00000, for 9 digit ZIP codes you can try 000000000
Do read Using Custom Cell Formats in Excel – Tips & Tricks to findout how to format dates, currencies, special formats etc.
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please signup below:
Your email address is safe with us. Our policies
More awesome tips for you:
 

Leave a Reply
Incredible hulk strikes IMDB – awesome online ad  Here is a simple way to get Rs. 70 Lakhs when you are 55 – DONT touch your PF 
26 Responses to “Formatting numbers in excel – few tips”
Smart tip PHD
[…] Original post by Chandoo […]
[…] To format a number as SSN, use the custom format code “000000000″… Get Full Tip 47. To format a phone number, use the custom format code “0000000000″… Get Full Tip […]
Dear Sir,
I have to write numbers in Excel as follow 1.11205080 but unfortunately I’m losing the last digit Zero, and if the cell format was changed to text to keep the last digit (Zero) i can’t find the needed number by using the control + F, and entering the the full number 1.11205080 now way to find it, but if I entered the number without the the last digit I can find it by using the control + F please advice.
Best Regards
Ahmad T.
Ahmad,
To create a generic custom format that would work for your problem above.
1 Go into format cell (ctrl + 1)
2Custom cells
3enter ‘0.00000000’ without the quotes (this can be modified to your specifications of digits before decimal and after).
Hope this helps..
Chandroo,
What great stuff!
I need to build a gantt chart template that will automatically fill in the duration blocks when I complete the Start and End dates. I would also like to add an actual End date and have that block filled in in RED.
Can you help?
Thanks in advance.
Steve
@Steve… Thank you so much and welcome to Chandoo.org.
I suggest you to see this page where you can find more about gantt charts : http://chandoo.org/wp/2009/06/16/ganttchartsprojectmanagement/
one cell like 24,25,26,27,28 : i want to direct () (maines) automatic funtion require.and this funtion is working than i want selec smone cell 59 one cell 24 is one sell than i want to sum is 35 ans please send me how to solve this problem.
please help me out,
how to write 123,456 lacs as 12.34 lacs and 12,345 thousand as 0.12 lacs with the help of number format. in same column / row.
i dont know vba.
— regards
i need the complete notes of Excel please can you help me
thanking you
[…] Formatting Numbers in Excel – an overview […]
So simple it’s stupid. I want to format a cell so that if I type in 34 it is entered as 3.4
first Email address incorrect.
hi chandoo
i have a query i work on a report which comes from oracle it has name and i have use text to column option but my data is not come in same cell.
Below is alternate formatting for the example above. Obviously, the number of spaces in the 2nd and 3rd examples will not align the numbers correctly unless a decimal is added to the format template for the entered numbers greater than or equal to 1.
[<1]_($#,##0.00_);_($#,##0_)
Orig#
Formatted
12
$12
12.3
$12
123.45
$123
0.84
$0.84
0.075
$0.08
[<1]_($#,##0.00_);_($#,##0″ “_)
Orig#
Formatted
12
$12
12.3
$12
123.45
$123
0.84
$0.84
0.075
$0.08
[<1]_($* #,##0.00_);_($* #,##0″ “_)
Orig#
Formatted
12
$ 12
12.3
$ 12
123.45
$ 123
0.84
$ 0.84
0.075
$ 0.08
[<1]_($* #,##0.00_);_($* #,##0.” “_)
Orig#
Formatted
12
$ 12.
12.3
$ 12.
123.45
$ 123.
0.84
$ 0.84
0.075
$ 0.08
In excel how can i enter my mobile 0509145493
@Shafeeq… Set cell formatting as text and type in the number.
What’s the easies way to get automatically 20.5 when I type 205?
Thanks
Ania
if 205 is in A1, enter the formula =LEFT(A1,2)&”.”&RIGHT(A1,1) wherever u want
Its very useful to me
Dear Chandoo,
thanks for the great site. I came across through Google.
Could you please help me with custom cell formatting in Excel? I need to format a number to have a superscript index, something like this: 2.54^a (I have no superscript here either). If I try 0.00″a” the “a” is not in superscript. Thank you very much in advance.
Need data value in format ‘123.456.789’ converted to format ‘123456.789’ . The acquisition system is putting a decimal point after every 3rd point. Please let me know, if you can help. Thanks!
@SP
If the number has two decimals it will be text
If it always has 9 digits you can use
=LEFT(A1,7)*1000+RIGHT(A1,4)
Greate tips….
HI FRIENDS ,
I WANT TO DISPLAY 27 DIGIT CODE FROM DATABASE EXPORTED CSV – EXCEL ,KINDLY DO THE NEEDFUL
If I have a program that makes a report, for example people’s ID NUMBERS in our office(we have 1 that is 011 and then we have another that is 11 and so on) when our program makes an excel csv file, how do u get the 0 in front of the people’s ID numbers? I know how to do it if all the numbers are the same amount of digits but they aren’t in this case. Thanks so much!