玛丽·安妮：
这将是一个伟大的时间使用VBA！但如果你不想，有一种方法来实现你的目标没有它。
你必须考虑到所有可能的结果。有4种不同的动物，这意味着你有15种结果：

你的公式只需要考虑所有15个。它非常长，因此被拉长了。因此，如果你有超过4个动物，你想变成短语，你应该走VBA路线。
以下是我的设置：

A7中的公式如下：

=IF(AND(A2<>"", A3="", A4="", A5=""), A2, IF(AND(A2="", A3<>"", A4="", A5=""), A3, IF(AND(A2="", A3="", A4<>"", A5=""), A4, IF(AND(A2="", A3="", A4="", A5<>""), A5, IF(AND(A2<>"", A3<>"", A4="", A5=""), A2&" and "&A3, IF(AND(A2<>"", A3="", A4<>"", A5=""), A2&" and "&A4, IF(AND(A2<>"", A3="", A4="", A5<>""), A2&" and "&A5, IF(AND(A2="", A3<>"", A4<>"", A5=""),A3&" and "&A4, IF(AND(A2="", A3<>"", A4="", A5<>""), A3&" and "&A5, IF(AND(A2="", A3="", A4<>"", A5<>""),A4&" and "&A5, IF(AND(A2<>"", A3<>"", A4<>"", A5=""), A2&", "&A3&", and "&A4, IF(AND(A2<>"", A3<>"", A4="", A5<>""), A2&", "&A3&", and "&A5, IF(AND(A2<>"", A3="", A4<>"", A5<>""), A2&", "&A4&", and "&A5, IF(AND(A2="", A3<>"", A4<>"", A5<>""), A3&", "&A4&", and "&A5, A2&", "&A3&", "&A4&", and "&A5))))))))))))))

以下是通过Excel实现的：

玛丽安妮-我是这样一个书呆子，我不得不这样做。这里是VBA解决方案，你可以有很多的名字，你想要的！粘贴此代码到一个新的模块中的工作簿（转到Developer -〉Visual Basic，然后插入-〉新模块，然后粘贴），然后你就可以在你的工作表中像一个常规函数一样使用它。只要给予它名字所在的范围，你就应该可以开始了！-马特

Function CreatePhrase(NamesRng As Range) As String
'Creates a comma-separated phrase given a list of words or names
Dim Cell As Range
Dim l As Long
Dim cp As String

'Add commas between the values in the cells
For Each Cell In NamesRng
    If Not IsEmpty(Cell) And Not Cell.Value = "" And Not Cell.Value = " " Then
        cp = cp & Cell.Value & ", "
    End If
Next Cell

'Remove trailing comma and space
If Right(cp, 2) = ", " Then cp = Left(cp, Len(cp) - 2)

'If there is only one value (no commas) then quit here
If InStr(1, cp, ",", vbTextCompare) = 0 Then
    CreatePhrase = cp
    Exit Function
End If

'Add "and" to the end of the phrase
For l = 1 To Len(cp)
    If Mid(cp, Len(cp) - l + 1, 1) = "," Then
        cp = Left(cp, Len(cp) - l + 2) & "and" & Right(cp, l - 1)
        Exit For
    End If
Next l

'If there are only two words or names (only one comma) then remove the comma
If InStr(InStr(1, cp, ",", vbTextCompare) + 1, cp, ",", vbTextCompare) = 0 Then
    cp = Left(cp, InStr(1, cp, ",", vbTextCompare) - 1) & Right(cp, Len(cp) - InStr(1, cp, ",", vbTextCompare))
End If

CreatePhrase = cp
End Function

希望这对你有帮助！Matt，viaExcelArchitect.com

使用新的TEXTJOIN函数，这可以非常容易地完成。
第1步：使用TEXTJOIN函数和", "分隔符，并将ignore_empty设置为TRUE。这将给予你逗号分隔的连接字符串，忽略空白值。
步骤2：使用COUNTA函数计算列表中非空条目的数量。然后从中减去1。此时您可能需要使用MAX函数将值降至1。
第3步：使用SUBSTITUTE函数将第2步中计算的逗号的最后一个示例替换为" and "。
把所有这些放在一起：

=SUBSTITUTE(TEXTJOIN(", ",TRUE,A1:A14),", "," and ",MAX(1,COUNTA(A1:A14)-1))

在上面的公式中插入任何你想要的Range而不是A1:A14，你会得到一个逗号分隔的连接，在最后一个单词之前有一个和。

VBA更简单。公式非常复杂，因为Excel没有允许连接范围的原生函数。但是，考虑到您已经编写了最多八个动物，可以使用以下公式连接A1的内容：A8根据您的规则。您可以在公式中的明显位置更改这些位置。
我做了一个改变：我可能是错的，但我相信英语规则表明最后一个and前面的逗号应该省略，所以我这样做了。如果必要的话，它可以添加。**编辑：**进一步的调查揭示了美国和英国规则之间的差异：美国的规则是你要求的，英国的规则省略了连词前的逗号。我将修改公式和UDF以符合美国的惯例。
在公式中，修改是将comma直接放置在and之前。UDF中的更改同样很小。
该公式由以下序列构建：

因此，把这些公式放在一起，以便只引用A1：A8，我们最终得到这个怪物：

=SUBSTITUTE(IFERROR(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2),",",",and ",LEN(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2))-LEN(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2),",",""))),MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","),2,LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(CONCATENATE(",",A1,",",A2,",",A3,",",A4,",",A5,",",A6,",",A7,",",A8,","),",,",","),",,",","),",,",","))-2)),",",", ")

这是一个VBA解决方案，它允许任何数量的项目;它根据与上述相同的规则连接。

Option Explicit
Function ConcatRangeWithAnd(RG As Range, Optional Delim As String = ", ")
    Dim COL As Collection
    Dim C As Range
    Dim S As String
    Dim I As Long

Set COL = New Collection
For Each C In RG
    If Len(C.Text) > 0 Then COL.Add C.Text
Next C

Select Case COL.Count
    Case 0
        Exit Function
    Case 1
        ConcatRangeWithAnd = COL(1)
    Case 2
        ConcatRangeWithAnd = COL(1) & " and " & COL(2)
    Case Else
        For I = 1 To COL.Count - 1
            S = S & COL(I) & ", "
        Next I
        ConcatRangeWithAnd = S & "and " & COL(COL.Count)
End Select

End Function

关于重复：
首先，我真的很喜欢Matt的解决方案，我已经将其添加到我的自定义函数集合中。
我所怀念的是从短语 * 中删除重复项而不从原始范围 * 中删除它们的可能性。
由于您无法创建虚拟范围（您可以在VBA中独立于源数据使用的范围），因此解决方案可能涉及将范围转换为数组，运行一些重复数据删除代码，然后从中创建短语。
我的解决方案（虽然不优雅）是使用UNIQUE和FILTER函数在电子表格的其他地方获得一个重复数据消除的列表（如果它困扰你，可以隐藏它），并使用Matt的函数。
=UNIQUE（FILTER（yourRange，yourRange〈〉"”））