How about
VBA Code:
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.Value = Evaluate(Replace("substitute(@,"","","" or"",len(@)-len(substitute(@,"","","""")))", "@", .Address))
End With
Ah
Not quite Fluff.
It turned this:
pickup
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, Hull
Blackburn, Blackpool, Bolton, Cleveleys, Preston, Wigan
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, Hull
Derby, Leicester (Fosse Park), Leicester, Loughborough, Milton Keynes, Northampton, Nottingham
Billingham, Darlington, Hartlepool, Peterlee, Stockton-on-Tees
Birkenhead, Liverpool, St Helens, Warrington
Acocks Green, Birmingham, Dudley, Solihull, Walsall, Wolverhampton
Gateshead, Heworth, Newcastle-upon-Tyne, North Shields, Wallsend, Whitley Bay
Gateshead, Heworth, Newcastle-upon-Tyne, North Shields, Wallsend, Whitley Bay
Castleford, Dewsbury, Leeds, Wakefield, York
Altrincham, Ashton-under-Lyne, Bury, Manchester, Oldham, Rochdale, Stockport
Middlesbrough, Thornaby
Altrincham, Ashton-under-Lyne, Bury, Manchester, Oldham, Rochdale, Stockport
Chesterfield, Doncaster, Mansfield, Rotherham, Sheffield, Sutton-in-Ashfield
Birkenhead, Liverpool, St Helens, Warrington
Blackburn, Blackpool, Bolton, Cleveleys, Preston, Wigan
Middlesbrough, Thornaby
Gateshead, Heworth, Newcastle-upon-Tyne, North Shields, Wallsend, Whitley Bay
Billingham, Darlington, Hartlepool, Peterlee, Stockton-on-Tees
Billingham, Darlington, Hartlepool, Peterlee, Stockton-on-Tees
Billingham, Darlington, Hartlepool, Peterlee, Stockton-on-Tees
Gateshead, Heworth, Newcastle-upon-Tyne, North Shields, Wallsend, Whitley Bay
Blackburn, Blackpool, Bolton, Cleveleys, Preston, Wigan
Altrincham, Ashton-under-Lyne, Bury, Manchester, Oldham, Rochdale, Stockport
Chesterfield, Doncaster, Mansfield, Rotherham, Sheffield, Sutton-in-Ashfield
Billingham, Darlington, Hartlepool, Peterlee, Stockton-on-Tees
Into this:
pickup
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
Anlaby, Beverley, Cottingham, Elloughton, Goole, Hessle, or Hull
etc
Apologies for not being more verbose in the original thread. Where I got the code from in my original post, the user there was trying to do roughly the same thing as me so I thought it would replace the last comma on a line-by-line basis.