How do I copy data from one cell to another.

[GMC]

Hello.

I'm having trouble creating a macro that will let me cut postcodes from one
cell and paste into another cell. I would appreciate if anybody can help
me.

What I have, is a large excel spreadsheet with a couple of thousand names
and addresses. The address fields start in column B through to G. Each line
of the address should be in a separate cell within each row, but the
postcodes have all ended up in the same cell as the last line of the
address. I need to cut the postcodes out of these cells and paste them into
column H which is empty.

The addresses are different lengths, leaving the postcodes in different
columns (usually columns D & E). The postcodes are not all the same size,
but the majority of them are 8 characters long. The postcode is basically
the last 8 characters in each cell.

What I want to be able to do, is to select the cell containing the postcode,
then run a macro that will remove it from the cell and paste it into the H
column on the same row.

I need the macro to work within any column and still cut and paste the last
eight characters to the H column.

Once I have this working I think I'll be able to work out how to modify the
macro to do the same for 7 or even 6 character long postcodes.

Any help is much appreciated.

Greg.

 

[Otto Moehrbach]

Say the last column of your address is Column E. Put the first formula in
Column F. This will produce all of Column E less the last 8 characters.
Put the second formula in Column G. It will produce the last 8 characters
of Column E. Copy both formulas down as far as you need. You should then
copy both columns and paste them in place, selecting Paste Special and
selecting Values. HTH Otto
=TRIM(LEFT(A5,LEN(A5)-8))
=RIGHT(A5,8)

 

[Davi]

You don't necessarily need a macro to do this...formulas should do the job.
Where either columnD OR columnE (you don't know which) contains the cell
with your concatenated address and postal code:

To get your 8-digit postal codes into columnH, use this formula in columnH:
=TRIM(IF(ISERROR(VALUE(RIGHT(D1,1))),RIGHT(E1,8),RIGHT(D1,8)))
[modify for other postal code lengths by replacing the 8's with the correct
length of the postal code]

To get your address w/o postal code, us this formula:

=TRIM(IF(ISERROR(VALUE(RIGHT(D1,1))),LEFT(E1,LEN(E1)-8),LEFT(D1,LEN(D1)-8)))
[same modify as above]