Insert and in Sentence Structure based on string values with commas

[mrmmickle1]

If I have strings that look like this that I'm trying to fit into a paragraph of text via code:

String 1 = "Methylphenidate, Nordiazepam-Diazepam, Fentanyl"

String 2 = "Methylphenidate, Nordiazepam-Diazepam, Fentanyl, THC"

String 3 = "Methylphenidate, Nordiazepam-Diazepam, Fentanyl, MDMA, THC"

How Can I alter them via VBA code to look like this so they will look proper in a sentence:

String 1 = "Methylphenidate, Nordiazepam-Diazepam and Fentanyl"

String 2 = "Methylphenidate, Nordiazepam-Diazepam, Fentanyl and THC"

String 3 = "Methylphenidate, Nordiazepam-Diazepam, Fentanyl, MDMA and THC"

Any help would be much appreciated.... My brain doesn't seem to be functioning properly today.

 

[Rick Rothstein]

I presume from your use of the word "strings" instead of text that this is a VBA question. Give this function a try...

Function Sentence(S As String) As String
  Dim LastComma As Long
  LastComma = InStrRev(S, ",")
  If InStr(1, Mid(S, LastComma), " and ", vbTextCompare) Then
    Sentence = S
  Else
    Sentence = Application.Replace(S, LastComma, 1, " and")
  End If
End Function

 

[mrmmickle1]

Thanks Rick,

Your function worked like a charm!!

I also used your other tip for adding commas in this Project as well :

IDs = IDs & "," & Cell.Value


Range("B2").Value = "'(" & Mid(IDs, 2) & ")"