Problems formating numbers with hyphens and leading zeroes.

jovogler

New Member
Joined
Feb 26, 2018
Messages
6
Hi,

I'm trying to format a list of phone numbers that are using this number format:
###-### ## ##

it starts with a zero and it keeps disappearing and when I enter two zeroes to make it appear there is a space that comes out of nowhere making the format look like this:
# ##-### ## ##

I don't understand how this space appears because it is not in the format. Because there are a lot of numbers it will save me a lot of time to simply change the format instead of going through every number and changing them.

Any advice or ideas how I can get rid of that annoying space?

I'm grateful for any help anyone can offer.
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Welcome to the forum.

If you want leading 0s to display, you should use this number format:

000-000 00 00
 
Upvote 0
Thanks, I've tried that already but with that format the leading zero does not show, and like I said adding more so it does show up creates this weird space.
 
Upvote 0
If the leading zero isn't showing with that format, your cells must contain text, not numbers.
 
Upvote 0
Ok, that's odd it only contains numbers. Using that format the number is supposed to look like this 010-111 11 11 but when I use it, it looks like this 10-1 111 111. No leading zero and the rest of the format looks completely different.
 
Upvote 0
What are your regional settings set to? Specifically, what do you use as the thousands grouping symbol?
 
Upvote 0
Swedish regional settings. The grouping symbol is blank or a space, we don't use any symbols here when writing numbers except to differentiate decimals.
 
Upvote 0
That would explain it then. Excel is treating the space in the number format as a thousands grouping symbol. Try using this instead:

000-000\ 00\ 00
 
Upvote 0
So that's the reason huh? It worked brilliantly! Thank you so much for the help this will make my day at the office tomorrow a lot less bothersome. :D
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top