Insert space before CAPITAL letters

[althea@ads-ny]

I have some very large files where some columns have the text without any
spaces. However they are using CAPS for all first letters. For example:

TheBookStore
WalmartDiscountStores

Is there a formula or macro that can add a space before every CAP (PROPER)
letter so that it will read:

The Book Store
Walmart Discount Stores

Thanks,
Althea

 

[Daniel.C]

Try :
Sub test()
Dim c As Range, i As Long, txt As String
For Each c In Range("A1", [A65000].End(xlUp))
txt = ""
For i = 1 To Len(c.Value)
If Mid(c, i, 1) = UCase(Mid(c, i, 1)) Then
txt = txt & " " & Mid(c, i, 1)
Else
txt = txt & Mid(c, i, 1)
End If
Next i
c.Value = Right(txt, Len(txt) - 1)
Next c
End Sub

Regards.
Daniel

 

[Chip Pearson]

Try code like the following. Select the cells you want to change and
then run the code:

Sub AAA()
Dim R As Range
Dim N As Long
Dim S As String
If TypeOf Selection Is Excel.Range Then
For Each R In Selection.Cells
If R.HasFormula = False Then
If R.HasArray = False Then
S = R.Text
For N = Asc("A") To Asc("Z")
S = Replace(S, Chr(N), " " & Chr(N))
Next N
S = LTrim(S)
R.Value = S
End If
End If
Next R
End If
End Sub


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[althea@ads-ny]

Chip,

You are a God-send!! Worked like a charm. I have thousands of lines in
many files and many columns and this will save me hours!!!

Thanks again!
Althea