Excel Formula: how to split string by capital lett

问题:

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)}

Any suggestion?

回答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.

My function:

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

What I get when i use it on excel

回答2:

You're pushing the envelope with this requirement. What you want to achieve requires looping over the same string repeatedly. That can only be done with recursion and Excel formulas don't do recursion.

With modern Excel 2016 you have Power Query (Get & Transform, or the add-in for Excel 2010 and 2013) and you can use that to write out the logic in M code if you don't want to use VBA. Power Query can be saved in a macro-free workbook and new data can be processed with the click of the "Refresh all" command in the ribbon.

回答3:

In B2:C28 fill in these :

A   ,A
B   ,B
C   ,C
D   ,D
E   ,E
F   ,F
G   ,G
H   ,H
I   ,I
J   ,J
K   ,K
L   ,L
M   ,M
N   ,N
O   ,O
P   ,P
Q   ,Q
R   ,R
S   ,S
T   ,T
U   ,U
V   ,V
W   ,W
X   ,X
Y   ,Y
Z   ,Z
 ,   

Note: B28 = , C28 =

then in A2 =SUBSTITUTE(A1,B2,C2) then drag until A28,

in A29 =RIGHT(A28,LEN(A28)-1) Done.

Hope that helps. (:

+------[edit]-----+

or in one line :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"Z",",Z"),"Y",",Y"),"X",",X"),"W",",W"),"V",",V"),"U",",U"),"T",",T"),"S",",S"),"R",",R"),"Q",",Q"),"P",",P"),"O",",O"),"N",",N"),"M",",M"),"L",",L"),"K",",K"),"J",",J"),"I",",I"),"H",",H"),"G",",G"),"F",",F"),"E",",E"),"D",",D"),"C",",C"),"B",",B"),"A",",A")," ,"," ")