VBA: Put a space after or before a upppercase letter in a string

Question

I have a sequence of texts that represent customers names, but they do not have spaces between each word/name (for example: JohnWilliamsSmith). However the words can be differentiated because the first letter of each word is uppercase.

So I need to transpose this list of customers strings to their regular format, with spaces between each word. So I want JohnWilliamsSmith to become John Williams Smith. However, I couldn't think of an immediate way to achieve this, and I believe that no combination of Excel formulas can offer this result.

Thus, I think the only solution would be to set up a Macro. It might be a Function to be used as a formula, or a code in module to work the data in a certain range (imagine that the list is in Range ("A2: A100")).

Does anyone have any idea how I can do this?

Have a look here: [Excel Regex](http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops). — Forty3, Apr 05 '17 at 17:03

score 3 · Accepted Answer · edited Apr 05 '17 at 17:18

3

Function AddSpaces(PValue As String) As String

  Dim xOut As String
  xOut = VBA.Left(PValue, 1)

  For i = 2 To VBA.Len(PValue)
    xAsc = VBA.Asc(VBA.Mid(PValue, i, 1))

    If xAsc >= 65 And xAsc <= 90 Then
      xOut = xOut & " " & VBA.Mid(PValue, i, 1)
    Else
      xOut = xOut & VBA.Mid(PValue, i, 1)
    End If

  Next
  AddSpaces = xOut
End Function

NB: Use this Function formula is =Addspace(A1).

edited Apr 05 '17 at 17:18

Scott Holtzman

27,099
5
37
72

answered Apr 05 '17 at 17:11

Rajesh Sinha

197
3
8

It worked perfectly! Thank you for the quick response! – J. L. Muller Apr 05 '17 at 17:30
Nice to see hear from you Tash. Keep asking. – Rajesh Sinha Apr 05 '17 at 17:45
Rajesh... could you please show me where should I adjust the code to add the spaces AFTER the uppercase letter instead? I've tried to analyze and figure out for myself, by this functions are still too advanced to me! – J. L. Muller Apr 06 '17 at 12:51
`xOut = xOut & " " & VBA.Mid(PValue, i, 1)` this line adds a space and then writes the uppercase letter. If you want to add the space **after**, which I'm not sure why you would want `J ohnW illiamsS mith`, but the line would be `xOut = xOut & VBA.Mid(PValue, i, 1) & " "` – interesting-name-here Apr 06 '17 at 13:35
Hey Trash, Gabriel has solved the problem. This code will put space after each character. Function InsertSpaces(S As String) As String InsertSpaces = Trim(Replace(StrConv(S, vbUnicode), Chr(0), " ")) End Function – Rajesh Sinha Apr 07 '17 at 17:20

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

In addition to @Forty3's comment to your question, the answer on how to use Regular Expressions in VBA is here.

With that being said you are then looking for the regular expression to match John, Williams, Smith which is ([A-Z])([a-z]+.*?)

Dim regex As New RegExp
Dim matches As MatchCollection
Dim match As match
Dim name As String


regex.Global = True
regex.Pattern = "([A-Z])([a-z]+.*?)"
name = "JohnWilliamsSmith"
Set matches = regex.Execute(name)

For Each match In matches
    name = Replace(name, match.Value, match.Value + " ")
Next match

name = Trim(name)

This gives me John Williams Smith. Of course, additional coding will be needed to account for cases like WillWilliamsWilliamson.

VBA: Put a space after or before a upppercase letter in a string

2 Answers2

Linked