Results 1 to 9 of 9

20020508, 02:19 #1
 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>

20020508, 04:19 #2
 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")

20020508, 04:36 #3
 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)

20020508, 04:45 #4
 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 3digitZiptoState crosswalk table. There are plenty of other approaches, I just happen to have and frequently use the attached.
John ... I float in liquid gardens
UTC 7ąDS

20020508, 05:59 #5
 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 addin and use the Histogram function, however this is a bit more complicated in setup and will give you the same information.
HIH

20020509, 00:12 #6
 Join Date
 Jan 2001
 Location
 West Long Branch, New Jersey, USA
 Posts
 1,931
 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 leftmost 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 5character 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 9digit 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

20040729, 11:10 #7
 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 "800150ABC,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 numbersI 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?

20040729, 11:32 #8
 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.

20040729, 11:35 #9
 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.