Solved: Excel cell formatting

[joemedinarios]

Quite an easy one for you super brains I think.

I'm trying to create a custom format for telephone numbers. I want the format to be:-

0123 4567 8901 2

The arrangement of the numbers will vary depending on the location of the number.

When I try to create a custom format it doesn't change the number, which currently has no spaces.

Thanks in advance

 

[draceplace]

This works for me ### #### #### #

 

[joemedinarios]

Thanks for that - This works fine for newly entered numbers but it doesn't edit numbers already on the spreadsheet.

 

[draceplace]

If you select the whole column (or row) and type in that format it should apply to all?

 

[joemedinarios]

It does for any new numbers entered into that column but not for the numbers already in it.

I've tried cutting the numbers out of the column and repasting them back in but to no avail.

Attached is a sample of data.

 

[draceplace]

Quit the issue here. You have numbers displayed as text and you did that because you want to display the leading "00". Formatting text is not what EXCEL was designed to do. Want to format a number and there are tons of options.

The only way I figure it out was to make a formula to re-display your values like this:
=TEXT(B2,"### #### #### #")
With B2 being your 'text phone number' Someone may come with better

 

[draceplace]

Wait that doesn't work either...It won't display the leading zero's and

="00" & TEXT(B2,"### #### #### #")
Is crap because you don't want to hard code the zeros...We need something other than the "#"???

 

[joemedinarios]

sorry to have created such a dilemma...maybe I shouldn't be allowed to use excel anymore!

 

[draceplace]

I think you have to let these be Numbers not Text. Excel seems to quit formating once it see it as text. As a number use this format:
000# #### #### #
You may have to play around with it to get the correct amount of leading zeros

 

[AcidBurnz95]

If your using Excel 2003, if you right click on the cell with the number and select convert to number then use the format that draceplace listed

 

[joemedinarios]

2007 I'm afraid

 

[AcidBurnz95]

should still work. The XLS I attached in my last post should work

 

[joemedinarios]

I still don't understand -

The column to the left has been formatted perfectly but if I try to format the column to the right, it wont work.

What am I doing wrong??

 

[joemedinarios]

got it - sorry thanks everybody!

 

[AcidBurnz95]

Sorry the right column was your orginal data the left column was the data i was working with.