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

Question

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

Navigation

#1 by (2 votes)

1

I have a sequence of texts that represent customer names, but they do not have enough space between each word / name (for example: CarlosAlbertoGomes ). However the words can be differentiated because the first letter of each word is capitalized.

Carlos Alberto Gomes would be Carlos Alberto Gomes . However, I could not think of any immediate way to achieve this and I believe that no combination of Excel formulas offers this in a native way.

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

Does anyone have any idea how I can do this?

string excel vba texto

asked by anonymous 05.04.2017 / 19:00

1 answer

How to omit certain arguments for which default values were given in the function's prototype parameters? move_uploaded_file failing

score 2 · Accepted Answer

I created this function in VBA for you:

It will check if each letter in the name is capitalized. If so, you'll 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 VBE , inserir um novo módulo and c olar esse código in the open window.

Afterinsertingthecode,the=separa_nomes()functioncanbeusedinyourspreadsheets.

Ex:

EDIT:Howdoesthiscodework?

Theideaistocreateafunctionthatcountshowmanycharacterseachwordhas
- Len(str)
Makealoopgoingletterbyletter
- Fori=1ToLen(str)
Iftheletterfoundisthefirstletteroftheword:savetheletterinavariablecalledtempanddonotputspacebefore.
- Ifi=1Thentemp=Mid(str,i,1)
Iftheletterfoundisuppercase:thismeansthatIneedtoputaspacebeforeitatthetimeofsavingitinthetempvariable.
- ElseIfMid(str,i,1)=UCase(Mid(str,i,1))Thentemp=temp+" " + Mid(str, i, 1)
Otherwise: I only 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 a capital letter (from before to after the uppercase letter), you must change the term:

From:

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) + " "