How to change number format # ### ### to ### ####?

[Lion2004]

Hi,

I'm using Office 2007.
I need to change in cell "A1" number format from 6 555 555 to 655 5555.

How can i do this change?

Thank you.
Peeter.

 

[Ms-Exl-Learner]

I am seeing the answer in your Subject itself.

Try this...

Now your Format is like this # ### ###
You just change it to ### ####

Place your cursor in A1 cell and do right click from your mouse and select
Format Cells>Number>Category>Custom>Type field past this ### ####

Now it will be changed.

If this post helps, Click Yes!

 

[Lion2004]

I know that it should be work like that, but it automatically change it back
to # ### ### after i click OK in Format Cells windows.

Any suggestions?

 

[David Biddulph]

Format Cells/ Number/ Custom

 

[Ms-Exl-Learner]

I think I found the answer.

I thought that it was formatted like # ### ###, but when I go through the
numbers once again (6 555 555) I found that the data consist manual spaces.

Just Select the Column in which the data are lying or select the range and
press Cntrl+H and in Find What Box give a space and in Replace with field
leave it as blank. Now hit Replace All. Finally Select the range and apply
the below format.

Format Cells>Number>Category>Custom>Type field past this ### ####

If this post helps, Click Yes!

 

[Lion2004]

There was made 1 replacement, but it still changes back format. I thought
maybe i should install SP2? I will try to do changes after i intalled SP2.
Will reply later.

 

[Ms-Exl-Learner]

Try this...

=TEXT(SUBSTITUTE(A1," ",""),"### ####")

copy and paste it to the remaining cells.

If this post helps, Click Yes!

 

[Lion2004]

It's doesn't working. I've installed SP2 and results are the same, no changes.
If i trying to change in "Cells Format" windows value ####### to # ######,
or to ## #####, or to #### ### it still show format # ### ### after i
clicking OK.

I've tried to create new emtpy document, the situation is the same!

Now i'm using Office 2007 SP2.

Peeter.

 

[Lion2004]

It works if i put into Customer format ###-####, but with space it doesn't
work.
Any suggestions?

 

[Lion2004]

Finally i found solution!
I put ###_##### into custom format and it display digits correctly.
I think it shows correctly cause in Regional Settings we have "Digit
grouping" option where is 9 digits))

Anyway thank you for your help!

Peeter

 

[Ms-Exl-Learner]

I am also having the same configuration and the above methods are yielding
the desired results to me. I don't know why you are not able to get the
result.

I hope that some of the Experts will see these posts and help you.

 

[Lion2004]

Solution was to use ###_##### in custom format.

I am also having the same configuration and the above methods are yielding
the desired results to me. I don't know why you are not able to get the
result.

I hope that some of the Experts will see these posts and help you.

 

[Ms-Exl-Learner]

Oh! ok.

Thanks intimating the solution to me, today I have learned one more thing by
way of you.