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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Welcome to the forum.

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

000-000 00 00
 

jovogler

New Member
Joined
Feb 26, 2018
Messages
6
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
If the leading zero isn't showing with that format, your cells must contain text, not numbers.
 

jovogler

New Member
Joined
Feb 26, 2018
Messages
6
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
What are your regional settings set to? Specifically, what do you use as the thousands grouping symbol?
 

jovogler

New Member
Joined
Feb 26, 2018
Messages
6
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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,351
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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
 

jovogler

New Member
Joined
Feb 26, 2018
Messages
6
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
 

Forum statistics

Threads
1,082,269
Messages
5,364,148
Members
400,783
Latest member
sambills

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top