Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    mountains, North Carolina, USA
    Posts
    64
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Picking data out of a large group (Excel 97 SR 2)

    I have a data list of about 1500 names and addresses. What I would like to do is get an idea of where these people are concentrated. I have sorted the data by zip code. I have also used the "TRUNC" formula and a divide the zip code data by 1000 to get a column with the first two numbers of the zip code. Other than going through the data line by line, is there a way I can generate a quick listing of how many lines I have in each area?

    One other thing, a few hundred more lines of data have the zip code listed in the zip+4 format. These entries are being treated as alphabetic data instead of as numeric data. Is there an easy way to strip off the +4 part of the zip and get the resulting data treated as a number

    In advance, thanks for any help. <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    Assuming the first two numbers of the zip code define the area you wish to count, then the COUNTIF function would work.

    COUNTIF(range,criteria)
    COUNTIF(b2:b1000,">0")

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    To remove the "+" sign from the zip you could use
    =RIGHT(text or cell,num_chars)
    where cell A1 is +4
    cell B1 could have the formula
    =RIGHT(A1,1) would retrurn the value 4 and be recognised as a number rather than text

    If you have entries that have different amount of digits or characters in them, (+4 and +423 and +44) you use LEFT(text or cell,num_chars) to get the first two characters. Then use the above RIGHT formular to get the second digit/character.

    LEFT and RIGHT can be used in the same formula
    =RIGHT(LEFT(A1,2),1)

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    I strongly recommend that you use 3 digit zips because of the way they are structured to aplly to MSA's. Attached is a sheet with some tools I use for similar purposes. Note the formula I use to extract states insulated against various zip layouts, including numeric, zip and text., and note the 3-digit-Zip-to-State crosswalk table. There are plenty of other approaches, I just happen to have and frequently use the attached.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    A PivotTable (DATA -> PivotTable Report) will give you a very fast and powerful method to analyse the distributions by ZIP code.

    Another method is to use the data analysis add-in and use the Histogram function, however this is a bit more complicated in setup and will give you the same information.

    HIH

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    Larry,

    I couldn't tell if you got the answer to your 2nd question about stripping off the +4 part of a zip+4 entry. Here's a few items:
    1. a formula like 1*Left(A1,5) will do 2 things: return the left-most 5 characters of the zip code (hence getting rid of the hyphen and next 4 digits), assuming its in A1, and then multiply this by 1 to convert the 5-character text string into a number. Once this is a number, you can do other things to analyze the data (eg, =TRUNC(b1/100) to get a 3 digit result).

    2. Your 9-digit zips could have been entered as numbers in the first place withOUT a hyphen. For display purposes, there is a Special format that inserts a hyphen between the 5th and 6th digit. But this is still a number. So you can do all the arithmetic operations you want on this.

    Fred

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    Paul,
    I have a very long column of numbers and text combined, such as "800150-ABC,fer,123,vvf". Each of the cells in the column contain the same number of numbers that lead off the cell (in tmy example the 800150), but may contain a different number of "trailing characters" (in my example, "-ABC,fer,123,vvf"). What I would like to do is in Column B, "strip out" the first 6 numbers-I think I can do this using the MID or LEFT finction. In addition, in Column C I want to insert the balance of the characters in the cell in Column A (in my example, "-ABC,fer,123,vvf"). Any ideas?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    If your data start in A1, enter this formula in B1:
    =LEFT(A1,6)
    The result will be text, although it looks like a number. If you need to have this as a number that can be used in calculations, use
    =0+LEFT(A1,6)
    an this in C1:
    =MID(A1,7,1000)
    The 1000 is an arbitrary number larger than the length of the longest possible "tail".
    Fill the formulas in B1 and C1 down as far as needed.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Picking data out of a large group (Excel 97 SR 2)

    Thanks, Hans.

Posting Permissions

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