Detecting commas in a cell and if more than one, replace the last with a "&"

[zoog25]

Hello all,

I have a program i'm writing in which a word document is filled in by content from specific cells. For a special case, there a column "H" which is either blank or contains a series of numbers that is separated by commas. What i would like to do is for the program to read this particular cell (Ie. say "H3") which would contain something like "18-20001442, 18-20000298, 17-20000477" and see that there are commas and replace the last one in this sequence with an "&" symbol so the new entry into the word document would be "18-20001442, 18-20000298 & 17-20000477". Please let me know if this is possible or what is the best way of doing this.

 

[Rick Rothstein]

Assuming all of your commas are always followed by a space, select the cells you want to modify and then run this macro...

Sub LastCommaBecomesAmpersand()
  Dim Cell As Range
  For Each Cell In Selection
    If Cell.Value Like "*,*" And Not Cell.Value Like "*&*" Then Cell.Value = Application.Substitute(Cell.Value, ",", " &", Len(Cell.Value) - Len(Replace(Cell.Value, ",", "")))
  Next
End Sub

 

[zoog25]

Hello Rick,

Thank you for the coding. I tested it and it worked. Thank you