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

zoog25

Active Member
Joined
Nov 21, 2011
Messages
331
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,425
Office Version
2010
Platform
Windows
Assuming all of your commas are always followed by a space, select the cells you want to modify and then run this macro...
VBA Code:
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

Active Member
Joined
Nov 21, 2011
Messages
331
Hello Rick,

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

Forum statistics

Threads
1,078,286
Messages
5,339,303
Members
399,292
Latest member
Bdbd55

Some videos you may like

This Week's Hot Topics

Top