Insert space before upper case letter

[zep]

Hey guys,

Since my last post, I have ruined, and saved a massive sheet I was working on. I think quite Excel and ruined my chanced of fixing my error.

I ran a script to pull the spaces out of every fourth cell in a column except it ran on all cells and before I realised it all my words were crammed together.

The only way I can think to fix this is my creating a script which inserts a space before each capital letter (as most are business names or addresses).

If anyone could help here, that be excellent.

Cheers

(I should really learn to write macros myself)

 

[pgc01]

Hi zep

This code inserts a space before each capital letter in column A:

Sub InsertSpace()
Dim rRng As Range, rCell As Range

Set rRng = Range("A1", Range("A" & Rows.Count).End(xlUp))

With CreateObject("vbscript.regexp")
    .Pattern = "([A-Z])"
    .Global = True
    
    For Each rCell In rRng
        rCell = .Replace(rCell, " $1")
    Next rCell
End With
End Sub

Hope this helps
PGC

 

[zep]

Thanks dude, worked a treat.