And using application.trim() would solve any multiple embedded spaces.

But I'm not sure if any code has multiple embedded spaces--so I chose not to use
it.

Rick Rothstein wrote:

mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

Another way to do this part of your code would be with this single line of
code...

mySplit = Split(Replace(.Cells(iRow, "B").Value, ", ", ","), ",")

Unless you are thinking there could be some cases where there are more than
a single space character after a comma (in which case your code would be the
way to handle it).

By the way, for those reading this thread who do not like functional steps
embedded within other functional steps, the one-liner statement above just
takes these two lines of code and combines them...

mySplit = Replace(.Cells(iRow, "B").Value, ", ", ",")
mySplit = Split(mySplit, ",")

I would also point out that IF the data is know to be "pure" (that is, it is
known that there is **always** a single space after the comma), then the
Replace function call is not needed... we could just use a comma/space pair
of characters as the delimiter like this...

mySplit = Split(.Cells(iRow, "B").Value, ", ")

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
One way that requires xl2k or higher:

Option Explicit
Sub testme()

Dim CurWks As Worksheet
Dim RptWks As Worksheet

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long

Dim DestCell As Range
Dim HowManyRows As Long

Dim mySplit As Variant
Dim pCtr As Long

Set CurWks = Worksheets("Sheet1")
Set RptWks = Worksheets.Add
Set DestCell = RptWks.Range("A1")

With CurWks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = FirstRow To LastRow
mySplit = Split(.Cells(iRow, "B").Value, ",")
For pCtr = LBound(mySplit) To UBound(mySplit)
mySplit(pCtr) = Trim(mySplit(pCtr))
Next pCtr

HowManyRows = UBound(mySplit) - LBound(mySplit) + 1

If HowManyRows 0 Then
DestCell.Resize(HowManyRows, 1).Value _
= .Cells(iRow, "A").Value
DestCell.Offset(0, 1).Resize(HowManyRows, 1).Value _
= Application.Transpose(mySplit)
Set DestCell = DestCell.Offset(HowManyRows, 0)
End If
Next iRow
End With
End Sub

I would assume that you're going to look at the code and return the ward
name
using =match().

Debra Dalgleish has lots of notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

I included Debra's page for =vlookup() if you decide to put the codes in
column
A and the wards in column B.

wrote:

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

--

Dave Peterson

--

Dave Peterson