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
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))))
- "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!
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?
Best Office Productivity Tools
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!