Solved: Excel cell formatting

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

joemedinarios

Thread Starter
Joined
Oct 1, 2008
Messages
7
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
 

joemedinarios

Thread Starter
Joined
Oct 1, 2008
Messages
7
Thanks for that - This works fine for newly entered numbers but it doesn't edit numbers already on the spreadsheet.
 
Joined
Jun 8, 2001
Messages
2,583
If you select the whole column (or row) and type in that format it should apply to all?
 

joemedinarios

Thread Starter
Joined
Oct 1, 2008
Messages
7
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.
 

Attachments

Joined
Jun 8, 2001
Messages
2,583
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
 
Joined
Jun 8, 2001
Messages
2,583
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

Thread Starter
Joined
Oct 1, 2008
Messages
7
sorry to have created such a dilemma...maybe I shouldn't be allowed to use excel anymore!
 
Joined
Jun 8, 2001
Messages
2,583
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
 
Joined
Jul 17, 2008
Messages
85
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
 

Attachments

joemedinarios

Thread Starter
Joined
Oct 1, 2008
Messages
7
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??
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top