CODEWITHSUNDEEP
excelexcel-formula
Stefano
Stefano

Reputation: 179

Excel Formula: how to split string by capital letters

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?

Upvotes: 0

Views: 4966

Answers (4)

Mohan C
Mohan C

Reputation: 93

This is what I use in access VBA Pass a string like ?GET_SPLIT_STRING("SplitAtCapitals") and get back the following Split At Capitals

Public Function GET_SPLIT_STRING(xStr As String) As String
    Dim i As Integer, xchar As String, ychar As String
    ychar = UCase(Left(xStr, 1))
    For i = 2 To Len(xStr) Step 1
        xchar = Mid(xStr, i, 1)

        If Asc(xchar) = Asc(UCase(xchar)) Then
            xchar = Space(1) & xchar
        End If
        ychar = ychar & xchar
    Next
    GET_SPLIT_STRING = ychar
End Function

Upvotes: 2

p._phidot_
p._phidot_

Reputation: 1950

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")," ,"," ")

Upvotes: 0

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

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

enter image description here

Upvotes: 3

teylyn
teylyn

Reputation: 35915

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.

Upvotes: 2

Related Questions