VBA Replace last comma in range of strings

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Hi guys, have seen this excellent line 'o code

Excel Formula:
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("substitute(@,"","","" or"",len(@)-len(substitute(@,"","","""")))", "@", .Address))
End With
 

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
With Range("A2", Range("A" & Rows.Count).End(xlUp))
   .Value = Evaluate(Replace("if({1},substitute(@,"","","" or"",len(@)-len(substitute(@,"","",""""))))", "@", .Address))
End With
 
Solution

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
Ok, how about
VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
64,110
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,144,614
Messages
5,725,319
Members
422,613
Latest member
salim9696

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top