Split Uppercase words in Excel

问题:

I would like to split all words in my cell by Uppercase, an example:

Original values:

MikeJones
RinaJonesJunior
MichealSamuelsLurth

Expected output:

Mike Jones
Rina Jones Junior
Micheal Samuels Lurth

Can this be done without using VBA?

回答1:

Having acknowledged Excellll's remarkable formula, the most efficient code solution would be RegExp based. This avoids long loops.

Function SplitCaps(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
    .Global = True
    .Pattern = "([a-z])([A-Z])"
    SplitCaps = .Replace(strIn, "$1 $2")
End With
End Function

回答2:

Here's a worksheet function solution. It ain't pretty, but if you're totally averse to using VBA, then I think you're stuck with ugly options only. For text in A1, paste the following into B1 and press Ctrl+Shift+Enter to enter the formula as an array formula:

=IFERROR(INDEX(IF(CODE(MID(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1))<=90,IF(CODE(MID(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1))>=65,IF(MID(D1,ROW(INDIRECT("A1:A"&LEN(D1)-1)),1)<>" ",REPLACE(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1," "&MID(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1)),D1),D1),D1),MIN(IF(CODE(MID(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1))<=90,IF(CODE(MID(D1,ROW(INDIRECT("A2:A"&LEN(D1))),1))>=65,IF(MID(D1,ROW(INDIRECT("A1:A"&LEN(D1)-1)),1)<>" ",ROW(INDIRECT("A1:A"&LEN(D1)-1)),2000000),2000000),2000000))),D1)

I told you it was ugly!

And for all that effort, that will only split the first and second name. For more splits, fill the formula over to the right. So for example, if you have a list of names in A1:A10, and you think the most words in any name is four, you could enter the formula in B1 (as an array formula!), fill down to B10, then fill right to E10. Your list of split names will be in E1:E10.

If you're inclined to jump down the rabbit hole, here's a brief explanation of what the formula does:

1. Check each character to see if it is in the ASCII range for capital letters and not preceded by a space. The first character of the name is skipped.
2. An array equal in size to the length of the string (minus 1) is populated as follows: If a match is found, the string is stored with the matching character replaced by a space preceding itself. If no match is found, the original string is stored.
3. The first element from this array that corresponds to a match is returned. If no match is found, the original string is returned.

回答3:

Since you say you would not like to use a VBA macro, but the problem requires VBA, I think a UDF will be a nice solution for you. This is a UDF (User defined Function) that you can use. Put this code in a general module of the same file that you have the data in.

Function splitbycaps(inputstr As String) As String

Dim i As Long
Dim temp As String

If inputstr = vbNullString Then
    splitbycaps = temp
    Exit Function
Else
    temp = inputstr
    For i = 1 To Len(temp)
        If Mid(temp, i, 1) = UCase(Mid(temp, i, 1)) Then
            If i <> 1 Then
                temp = Left(temp, i - 1) + " " + Right(temp, Len(temp) - i + 1)
                i = i + 1
            End If
        End If
    Next i
    splitbycaps = temp

End If
End Function

You can now use the function directly in a cell. Suppose you have data in A1 -> "MikeJones" And you want answer in cell A2. So in A2, you enter

=splitbycaps(A1)

And you will get your output. HTH.

回答4:

you have to do this with VBA.

Sub insertspaces()
Range("A1").Select
Do
    Row = ActiveCell.Row
    Column = ActiveCell.Column
    vlaue = ActiveCell.Value
    If vlaue = "" Then Exit Do
        Length = Len(vlaue)
        If Length > 1 Then
            For x = Length To 2 Step -1
            par = Mid(vlaue, x, 1)
            cod = Asc(par)
            If (cod > 64 And cod < 91) Or (cod > 191 And cod < 222) Then
            vlaue = Left(vlaue, x - 1) + " " + Mid(vlaue, x)
            End If
        Next
        ActiveCell.Value = vlaue
        End If
    Row = Row + 1
    Cells(Row, Column).Select
Loop
End Sub

回答5:

This would work as a user defined function.

Function SplitOnCapital(str As String) As String
    Dim letter As Byte, result As String

    For letter = 2 To Len(str)
        If Asc(VBA.Mid$(str, letter, 1)) < 90 Then //65 to 90 are char codes for A to Z
            result = WorksheetFunction.Replace(str, letter, 0, " ")
            letter = letter + 1
        End If
    Next letter

    SplitOnCapital = result
End Function

回答6:

Sub caps_small()

strIn = InputBox("Enter a string:")

For i = 1 To Len(strIn)

    If Mid(strIn, i, 1) Like "[A-Z]" Then
        cap = Mid(strIn, i, 1)
        capstr = capstr & cap

    ElseIf Mid(strIn, i, 1) Like "[a-z]" Then
        sml = Mid(strIn, i, 1)
        smlstr = smlstr & sml
    End If
Next

MsgBox capstr

MsgBox smlstr


End Sub