recursive parsing of zipcodes from a string
 

I have strings that are in Column A. These strings are composed of cities
and zip codes sample is below:
Castro Valley (94546), Castro Valley (94552), Danville (94526), Fremont
(94555), Hayward (94540), Hayward (94545), Hayward (94557), Oakland (94601),
San Leandro (94577), San Leandro (94578), San Lorenzo (94580), San Ramon
(94582), San Ramon (94583), Union City (94587)

The output would be something like:
94587, 94546, 94552, 94542, 94536, 94538, 94539, 94555, 94540, 94541, 94545,
94557, 94544, 94560, 94578, 94579, 94580

this would be one string, with each zip seperated by a comma

These can be various length of differant city/zip combos


I need to get a list of ZIPS seperated by a comma.

I would prefer to have it where one column would have the city/zip combo and
the next column would be just the zips

I posted this in the worksheet function group, but it may take some VBA to
do what I truely want to be done.


How can I do this/

Thanks
Bruce

recursive parsing of zipcodes from a string
 

Bruce,
This seems to pass a quick test :

Public Function GetZips(argIn As Variant, Optional ZipLength As Long = 5,
Optional Delim As String = "(") As String
Dim arr As Variant
Dim i As Long
Dim Temp As String

arr = Split(argIn, Delim)
'Miss out the first entry as it will be a city
For i = 1 To UBound(arr)
Temp = Temp & Left(arr(i), ZipLength) & ", "
Next

'remove the last 2 extra chars
GetZips = Left(Temp, Len(Temp) - 2)

End Function

NickHK

"Bruce" <oleexpres.at.johnsonclan.net wrote in message
...
I have strings that are in Column A. These strings are composed of cities
and zip codes sample is below:
Castro Valley (94546), Castro Valley (94552), Danville (94526), Fremont
(94555), Hayward (94540), Hayward (94545), Hayward (94557), Oakland
(94601),
San Leandro (94577), San Leandro (94578), San Lorenzo (94580), San Ramon
(94582), San Ramon (94583), Union City (94587)

The output would be something like:
94587, 94546, 94552, 94542, 94536, 94538, 94539, 94555, 94540, 94541,
94545,
94557, 94544, 94560, 94578, 94579, 94580

this would be one string, with each zip seperated by a comma

These can be various length of differant city/zip combos


I need to get a list of ZIPS seperated by a comma.

I would prefer to have it where one column would have the city/zip combo
and
the next column would be just the zips

I posted this in the worksheet function group, but it may take some VBA to
do what I truely want to be done.


How can I do this/

Thanks
Bruce

recursive parsing of zipcodes from a string
 

Thank you very much NichHK!
It does seem to work!
I am not familiar with "split" or uBound (I guess I don't know VBA as well
as I thought!)

Again, Thanks you very much!
Bruce


"NickHK" wrote in message
...
Bruce,
This seems to pass a quick test :

Public Function GetZips(argIn As Variant, Optional ZipLength As Long = 5,
Optional Delim As String = "(") As String
Dim arr As Variant
Dim i As Long
Dim Temp As String

arr = Split(argIn, Delim)
'Miss out the first entry as it will be a city
For i = 1 To UBound(arr)
Temp = Temp & Left(arr(i), ZipLength) & ", "
Next

'remove the last 2 extra chars
GetZips = Left(Temp, Len(Temp) - 2)

End Function

NickHK

"Bruce" <oleexpres.at.johnsonclan.net wrote in message
...
I have strings that are in Column A. These strings are composed of cities
and zip codes sample is below:
Castro Valley (94546), Castro Valley (94552), Danville (94526), Fremont
(94555), Hayward (94540), Hayward (94545), Hayward (94557), Oakland
(94601),
San Leandro (94577), San Leandro (94578), San Lorenzo (94580), San Ramon
(94582), San Ramon (94583), Union City (94587)

The output would be something like:
94587, 94546, 94552, 94542, 94536, 94538, 94539, 94555, 94540, 94541,
94545,
94557, 94544, 94560, 94578, 94579, 94580

this would be one string, with each zip seperated by a comma

These can be various length of differant city/zip combos


I need to get a list of ZIPS seperated by a comma.

I would prefer to have it where one column would have the city/zip combo
and
the next column would be just the zips

I posted this in the worksheet function group, but it may take some VBA
to
do what I truely want to be done.


How can I do this/

Thanks
Bruce