Purging ZIPCODES from a string of cities and zips

[Bruce]

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)

These can be various length of differant city/zip combos

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

How can I do this/

Thanks
Bruce

 

[Adrian#]

Hi

Select the column and click the Data menu and select "Text to Columns...".
This allows you to use the text wizard to separate data in a column into
many columns. Use the delimited option and enter the ( in other as the
separator. This will separte the City from the zipcode. Repeat the process
for the new column with zipcode and closing bracket to remove the ). You
should then have one column with the City and another column with the
zipcode. You could now use a couple of different methods to concatenate the
two columns back together and have a , as the separator. Hope this helps.
Adrian #

 

[macropod]

Hi Bruce,

Try Data|Text to Columns|Delimited, with the '(' as the separator. That will
put the zips into a separate column and delete the '('. Then all you need to
do is to use Find/Replace to delete all the ')' characters.

Cheers

 

[macropod]

Rest of solution:

After you've got the column of zips in Excel, copy & paste it into Word as
plain text, then use Find/Replace to change the paragraph marks (^p) to
commas.

Cheers

--
macropod
[MVP - Microsoft Word]

Hi Bruce,

Try Data|Text to Columns|Delimited, with the '(' as the separator. That will
put the zips into a separate column and delete the '('. Then all you need to
do is to use Find/Replace to delete all the ')' characters.

Cheers

--
macropod
[MVP - Microsoft Word]

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)

These can be various length of differant city/zip combos

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

How can I do this/

Thanks
Bruce

 

[Bruce]

Thank you for replying.

This does not do what I want it to do...
It creates many columns starting with the ZIP), CITY

What I need it to do is to take something like
Newark (94560), East Palo Alto (94303), Fremont (94536), Fremont (94538),
Fremont (94539), Fremont (94555), Hayward (94540), Hayward (94545), Hayward
(94557), Union City (94587)

and turn it into

94560, 94303, 94536, 94538, 94539, 94555, 94540, 94545, 94557, 94587

I have been using word (convert text to table then table to text, text to
table using the ( for the seperator, then cut column, convert to text, then
replace the )^p with a ,

This works, but is cludgy and I need to do this about 40-50 times a day.

I would ideally like to paste in one cell, and then the next cell would have
my desired out put in the next cell.

Thanks,
Bruce

 

[macropod]

Hi Bruce,

Since you're familiar with how to go about this in Word, have you considered
simplifying or automating the process there? For example, if you do a
Find/Replace with the Find text being '[ a-zA-Z ]', check the 'Use
wildcards' option have no Replace text, your string will go from
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)
to
(94546),(94552),(94526),(94555),(94540),(94545),(94557),(94601),(94577),(945
78),(94580),(94582),(94583),(94587)
in one step. From there it's a simple matter to delete all the '(' and ')'
characters. Someone better versed than I with wildacrd usage in Word may
even be able to tell you how to get rid of the '(' and ')' characters as
part of the wildcard Find/Replace.

Still in Word, it would be even easier to automate this via a macro coded
as:

Sub ZipCodeMacro()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "[ a-zA-Z ]"
.Replacement.Text = ""
.Forward = True
.Wrap = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
.Text = "("
.Forward = True
.Wrap = False
.MatchWildcards = False
.Execute Replace:=wdReplaceAll
.Text = ")"
.Forward = True
.Wrap = False
.Execute Replace:=wdReplaceAll
End With
End Sub

The above macro will act on any selected range.

Cheers

 

[Joerg]

Would a macro be OK?
Following macro strips all letters, spaces and brackets, but keeps the
commas:

Sub StripNonNumericals()
Dim RegExp As Object
Set RegExp = CreateObject("VBScript.RegExp")
For Each cell In Selection
With RegExp
.IgnoreCase = True
.Global = True
RegExp.Pattern = "[a-z ()]"
cell.Value = .Replace(cell.Value, vbNullString)
End With
Next cell
End Sub


Cheers,
Joerg

 

[macropod]

Hi Bruce,

Update:
In Word, if you do a Find/Replace with the "Find" text being '[ (a-zA-Z)]',
check the 'Use
wildcards' option and have no "Replace" text, your string will go from:
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)
to:
94546,94552,94526,94555,94540,94545,94557,94601,94577,94578,94580,94582,9458
3,94587
in one step.

The Word macro equivalent would be:
Sub ZipCodeMacro()
With Selection.Find
.ClearFormatting
.Replacement.ClearFormatting
.Text = "[ (a-zA-Z)]"
.Replacement.Text = ""
.Forward = True
.Wrap = False
.MatchWildcards = True
.Execute Replace:=wdReplaceAll
End With
End Sub

Cheers
--
macropod
[MVP - Microsoft Word]