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

[funguy]

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

 

[markmzz]

Here is a example with 27 words:

Layout

[TABLE="width: 1246"]
<tbody>[TR]
[TD="width: 843, bgcolor: transparent"]Result[/TD]
[TD="width: 819, bgcolor: transparent"]Data[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo10[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo10[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Roadbecomes Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoadbecomesMtVernonRoad[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Mt Vernon Road becomes Mt Vernon Road[/TD]
[TD="bgcolor: transparent"]MtVernonRoad becomes Mt Vernon Road[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]Wo1 Wo2 Wo3 Wo4 Wo5 Wo6 Wo7 Wo8 Wo9 Wo10 Wo11 Wo12 Wo13 Wo14 Wo15 Wo16 Wo17 Wo18 Wo19 Wo20 Wo21 Wo22 Wo23 Wo24 Wo25 Wo26 Wo27[/TD]
[TD="bgcolor: transparent"]Wo1Wo2Wo3Wo4Wo5Wo6Wo7Wo8Wo9Wo10Wo11Wo12Wo13Wo14Wo15Wo16Wo17Wo18Wo19Wo20Wo21Wo22Wo23Wo24Wo25Wo26Wo27[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*******************[/TD]
[TD="bgcolor: transparent"]******************[/TD]
[/TR]
</tbody>[/TABLE]

Formulas

[COLOR=#0000ff]In A2 [/COLOR]and copy down.

=LOOKUP(1,1/LEN($C2:$AB2),$C2:$AB2)

[COLOR=#0000ff]In C2 [/COLOR]([COLOR=#ff0000]use Ctrl+Shift+Enter and not only Enter to enter the formula[/COLOR]) and [COLOR=#ff0000]copy to the right until AB2 [/COLOR]and then down.

=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," "),"")

Markmzz