The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

The code Dave has given you will handle all of the above functionality for
you (actually, it eliminates the need to do the Text-To-Columns part
completely), but I just thought I would mention the following for you future
reference. Assuming your post codes are *always* delimited by a comma/space
(and never comma/multiple spaces), then instead of using a macro to remove
the leading spaces that result from the Text-To-Columns operation, you could
have done an Edit/Replace All on the original data first and then perform
the Text-To-Columns on that modified data... this way there would not have
been any leading spaces to need a macro for.

--
Rick (MVP - Excel)


wrote in message
...
On 6 July, 17:00, "Rick Rothstein"
wrote:
Clarification please... you say you have 2560 of these...

What I actually have to process is a spreadsheet full of local areas,
known as "wards". After each ward name is a postcode (Americans call
these "zip codes"), or usually several postcodes. A single row might
look like this:

Barton Ward CT6 5RT, CT6 4JL, CT6 7YY, CT6 8QW

That's one row. All the postcodes are in one cell, separated by
commas. In order to perform the look-up of the postcodes (on another
spreadsheet), I need it to look like:

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

The first bit is easy - I used Excel's Text-to-Columns feature to
split the postcodes into separate columns, and then I remove the
leading spaces (using a macro I already have).

So now I have:

Barton Ward CT6 5RT CT6 4JL CT6 7YY CT6 8QW

... and I need ...

Barton Ward CT6 5RT
Barton Ward CT6 4JL
Barton Ward CT6 7YY
Barton Ward CT6 8QW

I am aware that I could do this manually by inserting 3 more rows
underneath my one; then copying the last three postcodes, and doing
PasteSpecial (with Transpose) underneath CT6 5RT. Then copying
"Barton Ward" down three times.

However, with 2,000+ of these to do, I thought a VBA solution would
save a lot of time.

I'm just having trouble using the PasteSpecial feature in VBA,
really. The selection, inserting rows and copying work fine. But the
PasteSpecial gives an error. I was also wondering whether there might
be a more efficient, or easier, method than using PasteSpecial.

Can you suggest any method?

Steve