Solved

Separating Words with Caps in Excel

Posted on 2011-03-01
2
202 Views
Last Modified: 2012-05-11
I have a column of words in Excel that don't have spaces in them.
I.e.  ThisSentenceHasManyWords

I want to convert them to look like this:

This Sentence Has Many Words.
Is there a code I can put in a macro to do this?
0
Comment
Question by:ENTPF
2 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 35009719
Try this macro - you just need to select the data before running it.
Sub SpaceWords()
   Dim rCell As Range
   Dim strText As String
   Application.ScreenUpdating = False
   With CreateObject("vbscript.regexp")
       .Pattern = "([A-Z])"
       .IgnoreCase = False
       .Global = True
       For Each rCell In Selection
           rCell.Value = Mid$(.Replace(rCell.Value, " $1"), 2)
       Next rCell
   End With
   Application.ScreenUpdating = True
End Sub

Open in new window

0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35009760
rory's solution is much neater than mine, but since I did it I'll post it. A custom formula so you enter in a cell, e.g. =AddSpace(A1)
Function AddSpace(vIn) As String

Dim oRgx As Object, oMatches As Object, i As Long, s As String

With CreateObject("VBScript.RegExp")
    .Global = True
    .IgnoreCase = False
    .Pattern = "[A-Z]{1}[a-z]+"
    Set oMatches = .Execute(vIn)
End With

For i = 0 To oMatches.Count - 1
    s = s & " " & oMatches(i)
Next i

AddSpace = Trim(s)

End Function

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question