Trying to add spaces between capitalized words with a formula - DoesAnyoneKnowHow?

funguy

New Member
Joined
Feb 7, 2012
Messages
24
Hi, I'm trying to find a formula to add spaces in between capitalized words

for example :

cell A1 = MtVernonRoad

trying to make it say..

Mt Vernon Road


...any ideas ? Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Like so perhaps:

Code:
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
 
Upvote 0
Here is another UDF you can consider...

Code:
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

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use SplitOnCapital just like it was a built-in Excel function. For example,

=SplitOnCapital(A1)
 
Upvote 0
Maybe this:

Layout

ResultDataHelpColumns
Mt Vernon RoadMtVernonRoadMtVernon RoadMt Vernon Road
Wo1 Wo2 Wo3 Wo10Wo1Wo2Wo3Wo10Wo1Wo2Wo3 Wo10Wo1Wo2 Wo3 Wo10Wo1 Wo2 Wo3 Wo10
**************************************************************************************************************
<colgroup><col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;"> <col width="135" style="width: 101pt; mso-width-source: userset; mso-width-alt: 4937;"> <col width="142" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5193;" span="3"> <col width="19" style="width: 14pt; mso-width-source: userset; mso-width-alt: 694;" span="8"> <tbody> </tbody>


Formulas

Code:
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.

Markmzz
 
Upvote 0
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 ?



Maybe this:

Layout

ResultDataHelpColumns
Mt Vernon RoadMtVernonRoadMtVernon RoadMt Vernon Road
Wo1 Wo2 Wo3 Wo10Wo1Wo2Wo3Wo10Wo1Wo2Wo3 Wo10Wo1Wo2 Wo3 Wo10Wo1 Wo2 Wo3 Wo10
**************************************************************************************************************

<tbody>
</tbody>



Markmzz
 
Upvote 0
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 ?

Maybe this:

Layout

ResultDataHelpColumns
Mt Vernon RoadMtVernonRoadMtVernon RoadMt Vernon Road
Wo1 Wo2 Wo3 Wo10Wo1Wo2Wo3Wo10Wo1Wo2Wo3 Wo10Wo1Wo2 Wo3 Wo10Wo1 Wo2 Wo3 Wo10
**************************************************************************************************************

<tbody>
</tbody>


[/Code]

Markmzz
 
Upvote 0
Thanks for the UDFs guys , But I couldn't try the UDF because I the section where I needed to paste it gave an error. I did some research and it looks like I somehow did not install VBA when I initially installed Excel years back . So, I'll try and remedy that and then try it and report back on those.
 
Upvote 0
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 ?

Try this:

Formulas

Code:
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.

Markmzz
 
Upvote 0
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
 
Upvote 0
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

Look at this:

ResultDataHelpColumns
Mt Vernon RoadMtVernonRoadMtVernon RoadMt Vernon Road
Wo1 Wo2 Wo3 Wo10Wo1Wo2Wo3Wo10Wo1Wo2Wo3 Wo10Wo1Wo2 Wo3 Wo10Wo1 Wo2 Wo3 Wo10
Mt Vernon Roadbecomes Mt Vernon RoadMtVernonRoadbecomesMtVernonRoadMtVernonRoadbecomesMtVernon RoadMtVernonRoadbecomesMt Vernon RoadMtVernonRoadbecomes Mt Vernon RoadMtVernon Roadbecomes Mt Vernon RoadMt Vernon Roadbecomes Mt Vernon Road
Mt Vernon Road becomes Mt Vernon RoadMtVernonRoad becomes Mt Vernon RoadMtVernonRoad becomes Mt Vernon RoadMtVernonRoad becomes Mt Vernon RoadMtVernonRoad becomes Mt Vernon RoadMtVernon Road becomes Mt Vernon RoadMt Vernon Road becomes Mt Vernon Road
********************************************************************************************************************************************

<tbody>
</tbody>


You must to copy the second formula to the right until you want in the sheet. In my example, I copied the second formula until cell AB2.

Markmzz
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top