Help required with cut and paste macro.

[Ian Mac]

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.

Ian.

 

[Andy Brown]

The following is for UK postcodes, and assumes there are >= two bits of
address other than code -

=RIGHT(RIGHT(OFFSET(B1,0,COUNTA(B1:G1)-1),9),LEN(RIGHT(OFFSET(B1,0,COUNTA(B1
:G1)-1),9))-FIND(" ",RIGHT(OFFSET(B1,0,COUNTA(B1:G1)-1),9)))

NB - this is all one string, bar the space between the quotes after FIND.
Drag it down column H at will.

Rgds,
Andy

And while we're on matters postal, support Boris - visit
http://www.ncadc.org.uk/letters/newszine36/boris.html