Add Space To Each Uppercase Letter In Text

[SerenityNetwork]

I import a CSV file into Excel where the column title row has column titles that are just one long text string, without any spacing between the words. For example:
CompanySiteDescription
CompanySiteExternalSystemID
IssueNumber

I would like a method (formula or macro) that would add a space-character before each uppercase letter (that's not the first letter in the string or an uppercase letter that directly follows another upper case letter). Thus:
CompanySiteDescription becomes Company Site Description
CompanySiteExternalSystemID becomes Company Site External System ID
IssueNumber becomes Issue Number

Any code or examples on this will be greatly appreciated.

Thanks,
Andrew

[shg]

Write a function that loops through the string and uses a case statement; if the character is "A" to "Z", put a space followed by the character to the output string; anything else, put the character in the output string.

[SerenityNetwork]

Thank you.

If you had provided the tip on using a formula then I could probably figure it out. I'm generally handy at formulas. But I'm not handy at VBA. I'll try to give it a whirl in the next few days. I imagine that I'll have questions.

Thanks again,
Drew

[Andy Pope]

Here is a UDF.

B1: =splitcameltext(A1)

Code

Function SplitCamelText(TextToSplit As Variant, Optional Delimiter As Variant = " ") As Variant 
     
    Dim lngIndex As Long 
    Dim strText As String 
    Dim blnPreviousLetterUpper As Boolean 
     
    On  Error Goto Err_SplitCamelText 
     
    strText = TextToSplit 
    lngIndex = 2 
    Do While lngIndex <= Len(strText) 
        Select  Case Mid(strText, lngIndex, 1) 
        Case "A" To "Z" 
            If Not blnPreviousLetterUpper Then 
                strText = Left(strText, lngIndex - 1) & Delimiter & Mid(strText, lngIndex) 
            End If 
            blnPreviousLetterUpper = True 
        Case Else 
            blnPreviousLetterUpper = False 
        End Select 
        lngIndex = lngIndex + 1 
     Loop 
     
    SplitCamelText = strText 
    Exit Function 
     
Err_SplitCamelText: 
    SplitCamelText = XlCVError.xlErrValue 
    Exit Function 
End Function 

[Craig Ottley]

Andy how does the A to Z Work?

[Andy Pope]

The help file says

You also can specify ranges and multiple expressions for character strings. In the following example, Case matches strings that are exactly equal to everything, strings that fall between nuts and soup in alphabetic order, and the current value of TestItem:

Case "everything", "nuts" To "soup", TestItem

Basically it allows all characters between A and Z

[Craig Ottley]

handy tip to know... all this time i've been looping through an array of charactors

[Andy Pope]

works with a IF test as well

Sub X() 
     
    Dim strLetter As String 
     
    strLetter = "t" ' fails
    strLetter = "T" ' passes
     
    If strLetter >= "A" And strLetter <= "Z" Then 
         MsgBox " Upper case " & strLetter 
    Else 
        MsgBox "Something else" 
    End If 
     
End Sub 

[jindon]

UDF
=seren(A1)

Function seren(txt As String) As String 
    With CreateObject("vbscript.regexp") 
        .Pattern = "([A-Z])" 
        .Global = True 
        seren = Trim(.replace(txt, Chr(32) & "$1")) 
    End With 
End Function 

[SerenityNetwork]

Oh, wow! Thank you everyone. I was just updating my profile to better describe my VBA abilities (per shg's suggestion) and hadn't seen all the code samples you all have provided.

I don't have time to try them tonight, as I'm still working a late night project for my job, but I will get to it soon.

Thank you again. I'm very grateful.

Sincerely,
Andrew