Excel公式:如何用大写字母拆分字符串 [英] Excel Formula: how to split string by capital letters

问题描述

使用公式而不是VBA，我想提出一种解决方案，以拆分由多个单词组成的字符串.该公式应识别有大写字母的单词并将其分开.结果将是一个字符串，其中单词之间用，"分隔.

Using a formula, not VBA, I would like to come up with a solution to split a string composed of multiple words. The formula should recognize the words where there is a capital letter and separate them. The result would be a string where the words are separated by ",".

为澄清这是字符串的示例:

To clarify this is an example of the string:

Nursing StudentStudentNurseNursing School

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

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

I am trying the following formula but I can only isolate the first word:

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

有什么建议吗?

推荐答案

要实现此目的，您将需要纯VBA.创建一个自定义函数以在1个单元格中获取所需的字符串.然后，如果需要，请稍后使用文本到列".

To accomplish this, you will need pure VBA. Create a custom Function to get in 1 cell the string you want. Then, use Text to Columns later if you need it.

我的功能:

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上使用时会得到什么

What I get when i use it on excel

这篇关于Excel公式:如何用大写字母拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！