• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Separating Words with Caps in Excel

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
ENTPF
Asked:
ENTPF
1 Solution
 
Rory ArchibaldCommented:
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
 
StephenJRCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now