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

Result	Data
Mt Vernon Road	MtVernonRoad
Wo1 Wo2 Wo3 Wo10	Wo1Wo2Wo3Wo10
Mt Vernon Roadbecomes Mt Vernon Road	MtVernonRoadbecomesMtVernonRoad
Mt Vernon Road becomes Mt Vernon Road	MtVernonRoad becomes Mt Vernon Road
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	Wo1Wo2Wo3Wo4Wo5Wo6Wo7Wo8Wo9Wo10Wo11Wo12Wo13Wo14Wo15Wo16Wo17Wo18Wo19Wo20Wo21Wo22Wo23Wo24Wo25Wo26Wo27
*******************	******************

<tbody>
</tbody>

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