How to insert spaces before capital letters in excel?

Managing data formatting in Excel is a crucial aspect of ensuring that your spreadsheets are clear and professional. One common formatting need is inserting spaces before capital letters in text strings, which is often necessary when dealing with concatenated or improperly formatted data. This guide provides several effective methods to achieve this.

Insert spaces before every capital letter in Excel

• With a formula (Excel 2019 and later versions)
• With Kutools for Excel
• With User Defined Function
• With VBA code

Insert spaces before every capital letter with a formula (Excel 2019 and later versions)

Excel formulas provide a flexible way to insert spaces before capital letters. This method is ideal for users who prefer a non-programmatic approach.

Copy and paste the following formula into a blank cell, then press "Enter" key (Excel 365 or 2021) to get the first result. ( press "Ctrl" + "Shift" + "Enter" to apply it as an array formula if using Excel 2019)

=TRIM(LEFT(A2,1) & TEXTJOIN("", TRUE, IF(CODE(MID(A2, ROW(INDIRECT("2:" & LEN(A2))), 1))>=65, IF(CODE(MID(A2, ROW(INDIRECT("2:" & LEN(A2))), 1))<=90, " "&MID(A2, ROW(INDIRECT("2:" & LEN(A2))), 1), MID(A2, ROW(INDIRECT("2:" & LEN(A2))), 1)), MID(A2, ROW(INDIRECT("2:" & LEN(A2))), 1))))

Insert spaces before every capital letter with Kutools for Excel

"Kutools for Excel" offers a quick and effortless solution to insert spaces before every capital letter in your data. With its intuitive "Add Text" feature, you can automate this task in just a few clicks—no complex formulas or coding required!

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 "Before uppercase letters" from the drop down list.

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

Insert spaces before every capital letter with User Defined Function

For advanced users who need more flexibility, a User Defined Function (UDF) can be created to insert spaces before capital letters.

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
'Updateby Extendoffice
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 the below formula into a blank cell besides your data. Then drag the formula down to fill it to other cells, you will get the spaces inserted before every capital letter.

=addspaces(A2)

Insert spaces before every capital letter with VBA code

In this section, we’ll walk you through how to use VBA code to insert spaces before every capital letter in Excel. Whether you’re a beginner or an advanced user, this guide will provide you with the tools and knowledge to tackle this task efficiently.

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()
'Updateby Extendoffice
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:

Inserting spaces before every capital letter in Excel can be achieved through multiple methods, each with its own advantages. By following the steps and best practices outlined in this guide, you can optimize your workflow and handle this task with ease and efficiency. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

Related article:

How to add spaces after commas in Excel?