[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

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.
0
GKCU
Asked:
GKCU
  • 4
  • 3
1 Solution
 
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
 
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

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

=TEXT(A1,"#-000")
0
 
kgerbChief EngineerCommented:
Go to the formulas tab, formula auditing group, and make sure the Show Formulas button is not clicked.
Show Formulas button
0
 
GKCUAuthor Commented:
That was it.  Thank you.
0
 
kgerbChief EngineerCommented:
You're welcome.  Glad to help.
Kyle
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now