Removing certain things from 1 column

  • Thread starter Thread starter ABASSANETTI
  • Start date Start date
A

ABASSANETTI

I can't figure out how to remove a specific thing from my column..
have mailing addresses with number and street name in column a, cit
and state in cloumn b and mailing zip in column c... The zip cod
came in in zip+4 format... so it reads
91206-4695. The entire coloum is like that and I wanted to know i
there was any way to remove the -4695 through out the records. Not al
the of the zip codes are the same but they all contain the - with
numbers after. Is there any way to get rid of this
 
Create a blank Column to the right of your zips (if there isn't one already).
Use Data | Text to Columns
Separate on the -
Delete the Column with the 4 digits

tj
 
Experiment with this on a spare copy ..

Assuming the "mailing zip" in col C starts in C2 down

In an empty col to the right of the data, say col F:

Put in F2: =SUBSTITUTE(C2,MID(C2,SEARCH("-",TRIM(C2)),5),"")

Copy down as many rows as there is data in col C

Select the range in col F,
then do a copy > paste special > values
to overwrite the range in col C
 
Another formula to eliminate the unwanted characters........put in a blank
column and Copy down, then Copy > Paste special > Values............

=left(c1,5)

Vaya con Dios,
Chuck, CABGx3
 
this might help
for each c in selection
if right(c,4)="4695" then c.value=left(c,len(c)-5)
next
 
Back
Top