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

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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. ## 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. ## 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. ## 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. ## 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
•