1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel cell formatting

Discussion in 'Business Applications' started by joemedinarios, Oct 1, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. joemedinarios

    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
     
  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    This works for me ### #### #### #
     
  3. joemedinarios

    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.
     
  4. draceplace

    draceplace

    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?
     
  5. joemedinarios

    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.
     

    Attached Files:

  6. draceplace

    draceplace

    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
     
  7. draceplace

    draceplace

    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 "#"???
     
  8. joemedinarios

    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!
     
  9. draceplace

    draceplace

    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
     
  10. AcidBurnz95

    AcidBurnz95

    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
     

    Attached Files:

  11. joemedinarios

    joemedinarios Thread Starter

    Joined:
    Oct 1, 2008
    Messages:
    7
    2007 I'm afraid
     
  12. AcidBurnz95

    AcidBurnz95

    Joined:
    Jul 17, 2008
    Messages:
    85
    should still work. The XLS I attached in my last post should work
     
  13. joemedinarios

    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??
     
  14. joemedinarios

    joemedinarios Thread Starter

    Joined:
    Oct 1, 2008
    Messages:
    7
    got it - sorry thanks everybody!
     
  15. AcidBurnz95

    AcidBurnz95

    Joined:
    Jul 17, 2008
    Messages:
    85
    Sorry the right column was your orginal data the left column was the data i was working with.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/755064

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice