deleting particular stuff from cells

  • Thread starter Thread starter billrebels4
  • Start date Start date
B

billrebels4

i need to delete the last four digits of zip codes for about 15000
addresses... Is there anyway to do that all at once... zip exp:
39180-3454 i want to delete
the -3454.... i need to do this for about 15,000 zips... can i do it
automaticall without doing each cell one by one.....
 
Hi

Use a helper column alongside. In it type:

=MID(A2,1,LEN(A2)-5)

Once you have copied this down, copy it and use Paste/Special/Values to
replace the reference to the original cell. You can then delete your
original column.

Andy.
 
if all of the data has the nine digit zip you can try using "Text to
Columns" with "-" as the deliminator

other wise
=right(a1,5) in another cell would pull the first five numbers
 
Bill - are you not seeing the replies to your other posts - You have had this exact
question answered a number of times now.
 
Instead of deleting, how about just extracting the *first five* ?

With your data starting in A1, enter this in B1:

=LEFT(A1,5)

And copy down as needed.

If you really have 15,000 rows, then just select B1, and place the cursor
over the "drag handle" (little square in lower right of cell), until it
changes to a small black cross, and then double click.
This will automatically copy B1 down, as far as there is data in Column A.

I don't think you'll need these zips to be numbers, they'll sort with the
results you get from the text function "Left".

If for some reason you would need them as numbers, just prefix the formula
as below:

=--LEFT(A1,5)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



i need to delete the last four digits of zip codes for about 15000
addresses... Is there anyway to do that all at once... zip exp:
39180-3454 i want to delete
the -3454.... i need to do this for about 15,000 zips... can i do it
automaticall without doing each cell one by one.....
 
Just in case you didn't see the other reply:-

1) Separate the Zip and the routing number into 2 cells. To do this, make sure you have
a blank column to the right of the Zips, then select all the zips and do Data / Text to
Columns / Delimited / In the Other field put a - and then tick it. Hit OK and you will
now have them in their own cells. Select the entire set of data and sort on the zip.

2) Create a helper column with nothing but a zip in it, whilst leaving your original data
untouched. Assuming your first zip in F2, in G2 put the following formula and then just
copy it dowm:-

=LEFT(F2,LEN(F2)-5) which will give you the Zip in text format in each cell, or

=--LEFT(F2,LEN(F2)-5) which will give you the Zip in numberical format. Not that I can
think of a reason why a zip would need to be numeric, but just in case. :-)

Select the entire set of data and sort on the zip.

I don't know much about zips as i am in the UK, so ithey all need to be 5 instead of 4s
and 5s, you need to give me some examples of both and tell me what you need them all to
be.
 
Are all of the cells in a row? If they are have you tried building a macro that will go into the cell backspace over the unwanted digits and continue to the next cell?
 
Back
Top