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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

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