Unfortunately, there is no direct way for you to add spaces before the upper case letters in Excel, but, you can create User Defined Function to solve this task.

1. Activate your worksheet which contains the text strings you want to add spaces.

2. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

3. Click Insert > Module, and paste the following code in the Module window.

VBA code: Insert spaces before capital letters

Function AddSpaces(pValue As String) As String
'Update 20140723
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

4. Then save and close this code, go back to the worksheet, and enter this formula =addspaces(A1) into a blank cell besides your data, see screenshot:

5. And then drag the fill handle over the range that you want to contain this formula, you will get the spaces are inserted before your every capital letter.

Easily remove leading/trailing/extra spaces in cells

Kutools for Excel’s Remove Spaces utility enables Excel users to easily remove all leading space, trailing space, extra spaces, or all spaces from selected cells quickly.

Here is another VBA code can do you a favor, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module window.

VBA code: Insert spaces before capital letters

Sub AddSpacesRange()
'Update 20140723
Dim Rng As Range
Dim WorkRng As Range
Dim xOut As String
Dim xValue As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
For Each Rng In WorkRng
    xValue = Rng.Value
    xOut = VBA.Left(xValue, 1)
    For i = 2 To VBA.Len(xValue)
       xAsc = VBA.Asc(VBA.Mid(xValue, i, 1))
       If xAsc >= 65 And xAsc <= 90 Then
          xOut = xOut & " " & VBA.Mid(xValue, i, 1)
       Else
          xOut = xOut & VBA.Mid(xValue, i, 1)
       End If
    Next
    Rng.Value = xOut
Next
Application.ScreenUpdating = True
End Sub

3. Then press F5 key to execute this code, a prompt box will pop out to let you select a data range that you want to use.

4. And then click OK to close this prompt box, the spaces have been inserted before the capital letters at once, see screenshot:

Kutools for Excel’s Add Text utility can help you bypass the VBA macros and insert space before every capital letters easily in Excel.

1. Select the range where you will insert space before capital letters, and click the Kutools > Text > Add Text. See screenshot:

2. In the opening Add Text dialog box, type a space into the Text box, check the Only add to option and select 1st letter is uppercase from the Only add to drop down list.

3. Click the Ok button to insert space before every capital letters as following screen shot shown:

Notes: This method will also add space at the beginning of cells if the first letter is capital. You can apply Kutools > Text > Remove Spaces to remove all leading spaces from selected cells.

How to add spaces after commas in Excel?