Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    postal & telephone formatting (2000 9.0.4402 SR-1)

    I was given a large worksheet to be used to create a directory of names, addresses & phone numbers, etc.

    The person that created the sheet entered all the postal codes and phone number without spaces. For example, all the postal codes are in a single column and are 6 characters (ANANAN) e.g. A1B2C3. In Canada there should be a space between the first 3 characters and the last 3 e.g. A1B 2C3 for a total of 7 characters.

    I have a similar problem with all the phone numbers. They have all been entered in a single column as 10 (consecutive) digits (NNNNNNNNNN) e.g. 1234567890. Again, in Canada it is customary to separate the area code, exchange and number with dashes e.g. 123-456-7890 for a total of 12 characters.

    How can I change this data to appear correctly without having to edit thousands of individual cells?

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: postal & telephone formatting (2000 9.0.4402 SR-1)

    If a postal code is in A1, then this formula:

    <pre>=LEFT(A1,3)&" "&RIGHT(A1,3)
    </pre>


    will insert a space between the first and last three characters.

    This formula:

    <pre>=LEFT(A1,3)&"-"&MID(A1,3,3)&"-"&RIGHT(A1,4)
    </pre>


    will fix the telephone numbers.

    You can fill formulas like those down empty columns and then copy those columns and do a Paste Special/Values back over the original values to get constants instead of formulas.
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Etobicoke, Ontario, Canada
    Posts
    198
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: postal & telephone formatting (2000 9.0.4402 SR-1)

    Awesome! Worked like a charm. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •