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?
You can find the Split Columns > By Uppercase to Lowercase option in three places: Home tab—under the Split Column dropdown menu inside the Transform group. Transform tab—under the Split Column dropdown menu inside the Text Column group. Right-click a column—inside the Split Column option.
Select the cells in which you have the text that you want to split (in this case A2:A7). In the 'Data Tools' group, click on 'Text to Columns'. In the Convert Text to Columns Wizard: Step 1 of 3 of Text to Columns Wizard: Make sure Delimited is selected (it is the default selection).
In cell B2, type =PROPER(A2), then press Enter. This formula converts the name in cell A2 from uppercase to proper case. To convert the text to lowercase, type =LOWER(A2) instead. Use =UPPER(A2) in cases where you need to convert text to uppercase, replacing A2 with the appropriate cell reference.
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
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:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With