VBA: Putting a space before or after uppercase letters in a given text

Question

I have a string of texts that represent customer names, but which are without the proper spacing between each word/name (for example: Carlosalbertogomes). However the words can be differentiated because the first letter of each word is capitalized.

I needed to transpose this list of clients to their regular names, with spaces between each word, that is, Carlosalbertogomes will become Carlos Alberto Gomes. However, I couldn’t think of any immediate way to achieve this and I believe that no combination of Excel formulas offers it natively.

Thus, I believe that the only solution would be to appeal to VBA programming, or to a function to be used as a formula, or to a Macro in module to run over a certain range (suppose my text is in the Range("A2:A100")).

Does anyone have any idea how I can do that?

Answer 1

I created this function in VBA for you:

It will check that each letter in the name is capitalized. If so, it will add a space before the letter in question.

Code:

Function separa_nomes(str As String) As String

    Dim i As Integer, temp As String

    For i = 1 To Len(str)
        If i = 1 Then
            temp = Mid(str, i, 1)
        ElseIf Mid(str, i, 1) = UCase(Mid(str, i, 1)) Then
            temp = temp + " " + Mid(str, i, 1)
        Else
            temp = temp + Mid(str, i, 1)
        End If
    Next i

    separa_nomes = temp

End Function

---

To enable it, simply open the VBE, inserir um novo módulo and colar esse código in the open window.

After entering the code, the function =separa_nomes() can be used in your spreadsheets.

Ex.:

---

EDIT: How this code works?

• The idea is to create a function that counts how many characters each word has

  • Len(str)

• Make a loop going letter by letter

  • For i = 1 To Len(str)

• If the letter found is the first letter of the word: save the letter in a variable called temp and I don’t make room before.

  • If i = 1 Then temp = Mid(str, i, 1)

• If the letter found is uppercase: this means I need to put a space before it when storing it in the variable temp.

  • ElseIf Mid(str, i, 1) = UCase(Mid(str, i, 1)) Then temp = temp + " " + Mid(str, i, 1)

• Otherwise: I just copy the letter to the variable temp, without adding any space.

  • Else temp = temp + Mid(str, i, 1)

• Finally, I show the result accumulated in the variable temp in the cell where the formula was called.

  • separa_nomes = temp

---

That is, to change the position of the space when you find an uppercase letter (from before to after the uppercase letter), you must change the term:

Of:

ElseIf Mid(str, i, 1) = UCase(Mid(str, i, 1)) Then
    temp = temp + " " + Mid(str, i, 1)

To:

ElseIf Mid(str, i, 1) = UCase(Mid(str, i, 1)) Then
    temp = temp + Mid(str, i, 1) + " "