Option Explicit
Function SplitOnCapital(sText As String) As String
Dim lCt As Long
Dim sNewString As String
Dim sChar As String
For lCt = 1 To Len(sText)
sChar = Mid(sText, lCt, 1)
If UCase(sChar) = sChar Then
sNewString = sNewString & " " & sChar
Else
sNewString = sNewString & sChar
End If
Next
SplitOnCapital = Trim(sNewString)
End Function
Sub demo()
MsgBox SplitOnCapital("ThisIsAnExampleSentence")
End Sub
Function SplitOnCapital(sText As String) As String
Dim X As Long
SplitOnCapital = sText
For X = Len(SplitOnCapital) To 2 Step -1
If Mid(SplitOnCapital, X - 1, 2) Like "[a-z][A-Z]" Then SplitOnCapital = Application.Replace(SplitOnCapital, X, 0, " ")
Next
End Function
Result | Data | HelpColumns | ||||||||||
Mt Vernon Road | MtVernonRoad | MtVernon Road | Mt Vernon Road | |||||||||
Wo1 Wo2 Wo3 Wo10 | Wo1Wo2Wo3Wo10 | Wo1Wo2Wo3 Wo10 | Wo1Wo2 Wo3 Wo10 | Wo1 Wo2 Wo3 Wo10 | ||||||||
******************* | ****************** | ******************* | ******************* | ******************* | ** | ** | ** | ** | ** | ** | ** | ** |
In A2
=LOOKUP(1,1/LEN($C2:$M2),$C2:$M2)
In C2 - use Ctrl+Shift+Enter and not only Enter to enter the formula
=IFERROR(REPLACE(B2,LARGE(IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),COLUMNS($C2:C2)))>64,
IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),1))<91,ROW(INDIRECT("2:"&LEN(B2))))),COLUMNS($C2:C2)),0," "),"")
And copy to the right.
Maybe this:
Layout
Result Data HelpColumns Mt Vernon Road MtVernonRoad MtVernon Road Mt Vernon Road Wo1 Wo2 Wo3 Wo10 Wo1Wo2Wo3Wo10 Wo1Wo2Wo3 Wo10 Wo1Wo2 Wo3 Wo10 Wo1 Wo2 Wo3 Wo10 ******************* ****************** ******************* ******************* ******************* ** ** ** ** ** ** ** **
<tbody>
</tbody>
Markmzz
Maybe this:
Layout
Result Data HelpColumns Mt Vernon Road MtVernonRoad MtVernon Road Mt Vernon Road Wo1 Wo2 Wo3 Wo10 Wo1Wo2Wo3Wo10 Wo1Wo2Wo3 Wo10 Wo1Wo2 Wo3 Wo10 Wo1 Wo2 Wo3 Wo10 ******************* ****************** ******************* ******************* ******************* ** ** ** ** ** ** ** **
<tbody>
</tbody>
[/Code]
Markmzz
Markmzz, thanks. I tried this. Seems like a pretty amazing formula , but it only gave me the result with one space (MtVernon Road). In your example , what did you mean I should try putting in D2 ?
In A2 - use this
=LOOKUP(1,1/LEN($C2:$M2),$C2:$M2)
In C2 - use this
=IFERROR(REPLACE(B2,LARGE(IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),COLUMNS($C2:C2)))>64,
IF(CODE(MID(B2,ROW(INDIRECT("2:"&LEN(B2))),1))<91,ROW(INDIRECT("2:"&LEN(B2))))),COLUMNS($C2:C2)),0," "),"")
In D2 use this
=IFERROR(REPLACE(C2,LARGE(IF(CODE(MID(C2,ROW(INDIRECT("2:"&LEN(C2))),COLUMNS($C2:D2)))>64,
IF(CODE(MID(C2,ROW(INDIRECT("2:"&LEN(C2))),1))<91,ROW(INDIRECT("2:"&LEN(C2))))),COLUMNS($C2:D2)),0," "),"")
In E2 use this
=IFERROR(REPLACE(D2,LARGE(IF(CODE(MID(D2,ROW(INDIRECT("2:"&LEN(D2))),COLUMNS($C2:E2)))>64,
IF(CODE(MID(D2,ROW(INDIRECT("2:"&LEN(D2))),1))<91,ROW(INDIRECT("2:"&LEN(D2))))),COLUMNS($C2:E2)),0," "),"")
And so on (copy to the right). Then copy the formulas down.
Thanks! it's working amazingly for the 3 word string. But in an instance that there is a 4th word, it creates the spaces, but the first and second word revert to no space. example:
MtVernonRoad becomes Mt Vernon Road
but
MtVernonRoadWest becomes MtVernon Road West
Result | Data | HelpColumns | |||||||||||||||||||||||||
Mt Vernon Road | MtVernonRoad | MtVernon Road | Mt Vernon Road | ||||||||||||||||||||||||
Wo1 Wo2 Wo3 Wo10 | Wo1Wo2Wo3Wo10 | Wo1Wo2Wo3 Wo10 | Wo1Wo2 Wo3 Wo10 | Wo1 Wo2 Wo3 Wo10 | |||||||||||||||||||||||
Mt Vernon Roadbecomes Mt Vernon Road | MtVernonRoadbecomesMtVernonRoad | MtVernonRoadbecomesMtVernon Road | MtVernonRoadbecomesMt Vernon Road | MtVernonRoadbecomes Mt Vernon Road | MtVernon Roadbecomes Mt Vernon Road | Mt Vernon Roadbecomes Mt Vernon Road | |||||||||||||||||||||
Mt Vernon Road becomes Mt Vernon Road | MtVernonRoad becomes Mt Vernon Road | MtVernonRoad becomes Mt Vernon Road | MtVernonRoad becomes Mt Vernon Road | MtVernonRoad becomes Mt Vernon Road | MtVernon Road becomes Mt Vernon Road | Mt Vernon Road becomes Mt Vernon Road | |||||||||||||||||||||
******************* | ****************** | ******************* | ******************* | ******************* | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** | ** |