Split Uppercase words in Excel
Solution 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
Solution 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:
- 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.
- 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.
- The first element from this array that corresponds to a match is returned. If no match is found, the original string is returned.
Solution 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.
Solution 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
Visual Basic .Net
Updated on July 05, 2022Comments
-
Visual Basic .Net almost 2 years
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?
-
Alex P about 12 years+1 for great use of native excel functions. Don't know how you did it!
-
Doug Glancy about 12 years@Remnant, I did a post the other day that covered a fair amount of these functions, used on a similar problem. If you're interested it's at: yoursumbuddy.com/solving-the-npr-sunday-puzzle-3 . I was trying to put together an answer to this when I saw that Excellll had me beat by a mile :).
-
Doug Glancy about 12 yearsI was hoping you'd come along with a RegEx solution! It does seem like the way to go.
-
Siddharth Rout about 12 years+ 1 Nice One :-) Ugly? Well beauty lies in the eye of the beholder LOL +1 Doug ;)
-
Tom Wayson over 11 yearsUDF is definitely the way to go, but @brettdj 's regexp example below is prob more efficient. at least this one should start at char 2
-
user389419 over 11 yearsProblem is, your formula will insert spaces in legit abbreviations like "KFC" which will become "K F C".
-
radicand over 10 yearsThis also works as is (just paste in) in Google Spreadsheets.
-
d1ch0t0my over 7 yearsThis is very handy for me but how would I adapt this to split the caps but add 2 spaces rather than 1 e.g Mike Jones? TIA.
-
brettdj over 7 years@d1ch0t0my use
SplitCaps = .Replace(strIn, "$1 $2")
-
excelguy about 6 yearsNice answer, is there a way to include underscores and numbers to this function? ie replace the underscores with spaces, and split the numbers with spaces , similar to the caps.