Replace last comma with and in cell

[kelly mort]

<code style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace; vertical-align: baseline; max-height: 300px; overflow: auto;">

Sub [COLOR=#303336]example[/COLOR][COLOR=#303336]()[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] s1 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] s2 [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]String[/COLOR][COLOR=#303336]
[/COLOR][COLOR=#101094]Dim[/COLOR][COLOR=#303336] pos [/COLOR][COLOR=#101094]As[/COLOR][COLOR=#101094]Integer[/COLOR][COLOR=#303336]
    s1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ActiveCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value
    s2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#7D2727]""[/COLOR][COLOR=#303336]
    pos [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] InStr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] s1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]While[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]pos [/COLOR][COLOR=#303336]<>[/COLOR][COLOR=#7D2727]0[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        s2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] s2 [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] Mid[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]s1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] pos [/COLOR][COLOR=#303336]-[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]&[/COLOR][COLOR=#7D2727]","[/COLOR][COLOR=#303336]
        s1 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] Mid[/COLOR][COLOR=#303336]([/COLOR][COLOR=#303336]s1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] pos [/COLOR][COLOR=#303336]+[/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
        pos [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] InStr[/COLOR][COLOR=#303336]([/COLOR][COLOR=#7D2727]1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#303336] s1[/COLOR][COLOR=#303336],[/COLOR][COLOR=#7D2727]" "[/COLOR][COLOR=#303336])[/COLOR][COLOR=#303336]
    [/COLOR][COLOR=#101094]Wend[/COLOR][COLOR=#303336]
    s2 [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] s2 [/COLOR][COLOR=#303336]&[/COLOR][COLOR=#303336] s1
    ActiveSheet[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]ActiveCell[/COLOR][COLOR=#303336].[/COLOR][COLOR=#303336]Value [/COLOR][COLOR=#303336]=[/COLOR][COLOR=#303336] s2
[/COLOR][COLOR=#101094]End[/COLOR][COLOR=#101094]Sub
[/COLOR]

Found this code online and need help make it do what I want.

I want replace the last comma with "and" instead of this replacing all spaces with comma .

There may be :
1. No word
2. Only one word
3. Only two words
4. More than two words.

So I want all those factors considered.

Thanks </code>

 

[shg]

	A​	B​	C​
1​	Input​	Output​	​
2​	a	a	B2: =km(A2)
3​	a, b	a and b
4​	a, b, c	a, b and c

Function km(sInp As String) As String
  Dim i As Long
  
  i = InStrRev(sInp, ",")
  If i Then
    km = Left(sInp, i - 1) & " and" & Mid(sInp, i + 1)
  Else
    km = sInp
  End If
End Function

 

[jtakw]

Hi,

In case a formula solution is acceptable:

Book1
	A	B
7	a	a
8	a, b	a and b
9
10	a, b, c	a, b and c
11	a, b, c, d	a, b, c and d
Sheet622

Cell Formulas
Range	Formula
B7	=IF(A7="","",IFERROR(SUBSTITUTE(A7,","," and",LEN(A7)-LEN(SUBSTITUTE(A7,",",""))),A7))

 

[kelly mort]

Hello thanks to you both

Just that the solutions are just creating another set of problems for me.

This is what I really want to achieve
https://www.mrexcel.com/forum/excel...tems-separated-comma-when-conditions-met.html

 

[tyija1995]

Hi Kelly,

I have made a function and sub that will convert the final comma of a string into " and" - if no commas are present then the string will stay as is. Is this what you're looking for?

Function COUNTCOMMAS(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\,"
        COUNTCOMMAS = Len(txt) - Len(.Replace(txt, ""))
    End With
End Function


Sub ChangeLastComma()
On Error GoTo Capture
    Dim numCommas As Integer
    Dim txt As String
    Dim k As Integer
    Dim commaPosit As Integer
                
    txt = ActiveCell.Value
    numCommas = COUNTCOMMAS(txt)
    
        For k = 1 To numCommas
            commaPosit = WorksheetFunction.Search(",", txt, commaPosit + 1)
        Next k
        ActiveCell.Value = Left(txt, commaPosit - 1) & Replace(txt, ",", " and ", commaPosit)
        ActiveCell.Value = Application.Trim(ActiveCell.Value)
End


Capture:
    End
    
End Sub

Running this procedure will change only the active cell value.

 

[Scott Huish]

If that's what the op wants, then how about this:

Function ReplaceLastComma(r As String) As String
Dim x As Long
x = Len(r) - Len(Replace(r, ",", ""))
If x Then
    ReplaceLastComma = Application.WorksheetFunction.Substitute(r, ",", " and ", x)
Else
    ReplaceLastComma = r
End If
End Function

 

[kelly mort]

I am very sorry for not stating clearly what I wanted.

Meanwhile these codes have also been useful to me as the have solve some of my earlier challenges .

Thanks