VBA Replace last comma in range of strings

[RockandGrohl]

Hi guys, have seen this excellent line 'o code

Dim Addr As String
  Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(RIGHT(@)=""A"",LEFT(@,LEN(@)-1)&""B"",@)", "@", Addr))

But it's only for removing the rightmost letter.

I'm looking to remove the rightmost comma using InStrRev, which can be anywhere in a string and there is always at least 2 commas.

I would need to replace ", " with " or "

Cheers!

 

[Fluff]

How about

With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("substitute(@,"","","" or"",len(@)-len(substitute(@,"","","""")))", "@", .Address))
End With

 

[RockandGrohl]

How about

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.

 

[Fluff]

Ok, how about

With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("if({1},substitute(@,"","","" or"",len(@)-len(substitute(@,"","",""""))))", "@", .Address))
End With

 

[RockandGrohl]

Ok, how about

With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("if({1},substitute(@,"","","" or"",len(@)-len(substitute(@,"","",""""))))", "@", .Address))
End With

Absolutely marvellous, another head-scratching implementation of Evaluate. What an amazing tool.

Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.