Converting a number to text in the format "0## ### ####

[Alex11]

In the old versons of excell I was able to format a number such as a cell number to start with 0 and then break the number down to have a space after the third and sixth digit via the custom text field in format number using 0## ### ####. Simelarly I could achieve the same effect with text(A1,"0## ### ####"). Now the result is "0 ### ### ###. I cant get rid of the space after the zero and make the number look like a cell number anymore. Any solution?

 

[Minkowski]

play a little with the #

depending on the digit count
example

123456789 (9digit number)

0### ### #### this would give you
012 345 6789

with no dash after 0

just start doing this from left to right

 

[Alex11]

Thanks for the reply. My nine digit cel number stil gives me a space in the wrong space regardless of the additional # digits. I cannot add or remove any #s and get the result I want.

This problem only came in after Windows XP, because with Office 2007 in windows XP was still fine, with the format 0## ### ####. Windows Vista and Windows 7 somehow works differently.

Any ideas

 

[VoG]

Working here (Excel 2010, Windows 7)

Excel Workbook
	A	B
1	123456789	012 345 6789
Sheet3

 

[Zack Barresse]

Nope, doesn't work differently. It must be your data. I would venture a format, or perhaps something swapped between text and numbers, has changed. Is your number an actual number? Give us some examples. It looks like you're wanting to cut off the first digit and use a manual 0 instead?

 

[Alex11]

No its not the data. I have two scenarios.
1. I format collumn A in the usual way ie select custom
and enter 0## ### ####

Then wheteher I have pre-populated the range or enter it subsequently the result is 0 123 456 789

I then recheck the custom format and find that the format has changed to
0 ### ### ###.

2. I enter 123456789 in general format and enter the formula in an adjacent cell =text(A1,"0## ### ####")

Once again I get 0 123 456 789

This issue is not only on my own machine its been the case with other people's machines too.

The concept of achieving this result was successfully used in both ways until other prople told me their machines worked differently some time ago.

I run MS Office 2010 in Windows 7 64 bit. I dont know if this has any impact at all. My regional settings are set up for South Africa, once again irrevelent.

 

[VoG]

For me using the same versions of Excel and Windows as you I get the same result with a Custom format as I got using the TEXT formula.

Maybe it is related to regional settings?

 

[Alex11]

Thanks. It would appear my problem lies in the regional settings. My experience in changing these settings is pretty much changing the date format to be compatible with the South African date formats i.e. dd-mmm-yy instead of mm-dd-yy.
My settings are geared towards English South Africa, butsomehow doubt that this is the root of the problem.
I experimented with the digit grouping, but still no joy.
Surely I cant be the only one who reported this issue and who are the Regional Setting Doctors out there.

Thanks again
Alex

 

[Zack Barresse]

So if you change your regional settings this issue goes away?

 

[Alex11]

No unfortunately not still sitting with the same problem. Any ideas?