Skip to main content

How to insert spaces before capital letters in excel?

Author: Xiaoyang Last Modified: 2025-04-07

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 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 capital letters with a formula

Explanation of this formula:
  • "LEFT(A2,1)": Retrieves the first character of the string as is.
  • "INDIRECT("2:" & LEN(A2))": Creates a range of numbers from the 2nd character to the last character.
  • "MID(A2, ROW(...), 1)": Extracts each character one by one from the second character onward.
  • "CODE(...)": Checks the ASCII value of the character to determine if it's a capital letter (between 65 and 90).
  • "TEXTJOIN("", TRUE, ...)": Combines the characters into a single string, inserting a space before any capital letter.
  • "TRIM(...)": Removes any extra spaces at the start or end of the resulting string.

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!

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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

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.
specify the options in the dialog box

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


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 space before every capital letters by User Defined Function

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.
vba code to select the data range

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

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?


Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!