excel - Excel公式：如何用大写字母分割字符串

Question

使用公式而不是 VBA，我想提出一个解决方案来拆分由多个单词组成的字符串。公式应该识别有大写字母的单词并将它们分开。结果将是一个字符串，其中单词由“，”分隔。

为了澄清这是一个字符串的例子：

Nursing StudentStudentNurseNursing School

Desired Result:
Nursing Student,Student,Nurse,Nursing School

我正在尝试以下公式，但我只能隔离第一个单词：

{=LEFT(Q4,SMALL(FIND(CHAR(ROW(INDIRECT("65:90"))),Q4&"ABCDEFGHIJKLMNOPQRSTUVWXYZ"),2)-1)}

有什么建议吗？

标签： excelexcel-formula

Answer 1

为此，您将需要纯 VBA。创建一个自定义函数以在 1 个单元格中获取所需的字符串。然后，如果需要，稍后使用 Text to Columns。

我的功能：

Public Function GET_STRING(ByVal ThisCell As Range) As String
Dim i As Integer

Dim MyPositions As String
Dim ArrPositions As Variant

For i = 2 To Len(ThisCell.Value) Step 1
    If Mid(ThisCell.Value, i, 1) = UCase(Mid(ThisCell.Value, i, 1)) And _
    Mid(ThisCell.Value, i, 1) <> " " And Left(Mid(ThisCell.Value, i - 1, 1), 1) <> " " Then MyPositions = MyPositions & i & ";"
Next i

ArrPositions = Split(Left(MyPositions, Len(MyPositions) - 1), ";")

For i = 0 To UBound(ArrPositions) Step 1
    If i = 0 Then
        GET_STRING = Left(ThisCell.Value, ArrPositions(i) - 1) & "," & Mid(ThisCell.Value, ArrPositions(i), ArrPositions(i + 1) - ArrPositions(i))
    ElseIf i <> UBound(ArrPositions) Then
        GET_STRING = GET_STRING & "," & Mid(ThisCell.Value, ArrPositions(i), ArrPositions(i + 1) - ArrPositions(i))
    Else
        GET_STRING = GET_STRING & "," & Mid(ThisCell.Value, ArrPositions(i), Len(ThisCell.Value) - ArrPositions(i) + 1)
    End If
Next i

End Function

当我在excel上使用它时我得到了什么