Add space in cell when in one word something starting with Capital

[jellevansoelen]

Hi,

I have a large Excel file with a lot of this cells:

Book2
	A
1	Build house / buildingDowntown 12, Amsterdam
2	Move barn by usThe Wolfstreet 14, Rotterdam
3	Set commercialSquare 12, Den Haag
Sheet1

There was a conversion error, which put the street directly after the last word.
Now i'm looking for a solution which put a space for this street.
I'm looking for a script which put a space when in the middle of a word is being a capital.

You can see it in the example by the red bold letters:

Book2
	A
1	Build house / buildingDowntown 12, Amsterdam
2	Move barn by usThe Wolfstreet 14, Rotterdam
3	Set commercialSquare 12, Den Haag
Sheet1

I want that the cells looking on this way:

Book2
	A
1	Build house / building Downtown 12, Amsterdam
2	Move barn by us The Wolfstreet 14, Rotterdam
3	Set commercial Square 12, Den Haag
Sheet1

Is there some possibility to do that?

Tnx,

 

[jellevansoelen]

Sorry, this is the second image:
You can see it in the example by the red bold letters:

Build house / buildingDowntown 12, Amsterdam
Move barn by usThe Wolfstreet 14, Rotterdam
Set commercialSquare 12, Den Haag

 

[jtakw]

Hi,

This is one way, array formula to be confirmed by CSE:

Book3.xlsx
	A	B
1	Build house / buildingDowntown 12, Amsterdam	Build house / building Downtown 12, Amsterdam
2	Move barn by usThe Wolfstreet 14, Rotterdam	Move barn by us The Wolfstreet 14, Rotterdam
3	Set commercialSquare 12, Den Haag	Set commercial Square 12, Den Haag
Sheet865

Cell Formulas
Range	Formula
B1:B3	B1	=REPLACE(A1,MIN(FIND(CHAR(ROW($65:$90)),A1&CHAR(ROW($65:$90)),2)),," ")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JGordon11]

This vba code will change the selected cells (must be cells in one column only selected) by adding a comma and a space before the first capital letter occurring on or after position 2. Try it on a copy of your spreadsheet.

Sub AddSpaces()
    If Selection.Columns.Count > 1 Then Exit Sub
    Dim a As Variant, j As Long, s As Variant, i As Long, found As Boolean, cd As Integer, ls As Integer
    a = Selection.Value
    For Each s In a
        j = j + 1
        found = False
        i = 2
        ls = Len(s)
        Do While Not found And i <= ls
            cd = Asc(Mid(s, i, 1))
            If cd >= Asc("A") And cd <= Asc("Z") Then
                a(j, 1) = Mid(s, 1, i - 1) & ", " & Mid(s, i)
                found = True
            Else
                i = i + 1
            End If
        Loop
    Next
    Selection = a
End Sub

 

[jtakw]

If you want a comma & space, just add the comma to my formula in Post #3 like below, otherwise, if you only want the space, as you stated in OP, use formula in Post #3 as-is.

Book3.xlsx
	A	B
1	Build house / buildingDowntown 12, Amsterdam	Build house / building, Downtown 12, Amsterdam
2	Move barn by usThe Wolfstreet 14, Rotterdam	Move barn by us, The Wolfstreet 14, Rotterdam
3	Set commercialSquare 12, Den Haag	Set commercial, Square 12, Den Haag
Sheet865

Cell Formulas
Range	Formula
B1:B3	B1	=REPLACE(A1,MIN(FIND(CHAR(ROW($65:$90)),A1&CHAR(ROW($65:$90)),2)),,", ")
Press CTRL+SHIFT+ENTER to enter array formulas.