Excel Value not formatting with cell format change

I have an excel sheet with numbers that look like this:

7137000
7137012
5465000
5465010

I want the numbers to look like this:

7137-000
7137-012
5465-000
5465-010

I go into Format Cell and put

#-###

Inside the the format cell properties it shows the number as 7137-000, but when I hit ok, the numbers don't change.  They stay exactly like they were before. This is in MS Excel 2010.  I already checked and the cell is not protected.
LVL 1
GKCUAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kgerbConnect With a Mentor Chief EngineerCommented:
Go to the formulas tab, formula auditing group, and make sure the Show Formulas button is not clicked.
Show Formulas button
0
 
kgerbChief EngineerCommented:
Try #-000.  It worked for me in XL2007.

Kyle
0
 
GKCUAuthor Commented:
Nope.  Cells stayed the same:

7137000
7137012
5465000
5465010


Though I am sure your way would make my numbers look like this:

7137000-000
When I want it to look like

7137-000
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
kgerbChief EngineerCommented:
Nope, #-000 will work.  Try this.  In another cell point to one of your numbers with this formula.

=TEXT(A1,"#-000")

It might be that there is a hidden character that is making excel think the numbers are text.

Kyle
0
 
GKCUAuthor Commented:
Ok.  When I do what you suggest, the cell displays:

=TEXT(A1,"#-000")
0
 
GKCUAuthor Commented:
That was it.  Thank you.
0
 
kgerbChief EngineerCommented:
You're welcome.  Glad to help.
Kyle
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.