How to insert spaces before capital letters in excel?
Properly formatting data in Excel is essential for clarity and ease of analysis, particularly when dealing with imported or concatenated text strings that lack readability. A typical situation you may encounter is text where words are combined without spaces, such as "DataFormattingTool", making it hard to read or process. Inserting spaces before each capital letter helps separate the words, making your spreadsheets more user-friendly and presentable for both data interpretation and sharing purposes. This guide explores multiple practical methods you can use to quickly add spaces before every capital letter in Excel, ensuring your data remains organized and easily manageable in various scenarios.
Insert spaces before every capital letter in Excel
Insert spaces before every capital letter with a formula (Excel 2019 and later versions)
Using Excel formulas is a straightforward and reliable way to insert spaces before capital letters. This approach doesn't require any programming skills or special tools and is especially suited for users who want to automate the process with built-in Excel functions. The following method leverages the modern dynamic array and text manipulation functions available in Excel 2019, Excel 2021, and Microsoft 365.
Applicable scenario: Ideal for users with up-to-date versions of Excel who need a quick, formula-based solution, and wish to avoid VBA or add-ins. This method is advantageous because it updates automatically with cell changes or when data is pasted in, but may not be compatible with older versions of Excel.
To implement, follow these steps:
1. Copy and paste the following formula into a blank cell (for example, cell B2 if your original text is in cell A2), then press Enter to get the first result in Excel 365 or 2021. For Excel 2019, use Ctrl+Shift+Enter to apply it as an array formula:
=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))))
2. After obtaining the first result, drag the fill handle down to apply the formula to other rows as needed. The output will display your original text with spaces inserted before each capital letter.
- LEFT(A2,1): Returns the first character of the string unchanged, avoiding an unnecessary space at the start.
- INDIRECT("2:" & LEN(A2)): Generates a sequence referring to each subsequent character by position, from the second character to the end.
- MID(A2, ROW(...),1): Extracts one character at a time based on the position.
- CODE(...): Evaluates each extracted character to check its ASCII value; values between 65 and 90 correspond to uppercase English letters (A-Z).
- TEXTJOIN("", TRUE, ...): Concatenates each character, inserting a space before those identified as capital letters, and skips any empty text when TRUE is specified.
- TRIM(...): Removes leading and trailing spaces that may be added in the process.
Note: Errors may occur if the original cell is empty or contains non-text values. Ensure your data is text-formatted for the best results. When dragging the formula, check that cell references are correct and adjust them if your data starts from a different row or column.
Insert spaces before every capital letter with Kutools for Excel
"Kutools for Excel" provides a highly efficient way to insert spaces before every capital letter throughout your selected data range. Its built-in "Add Text" feature makes the process straightforward, offering a practical alternative for users who want a few clicks to complete the task—without the need to remember complex formulas or use code.
Applicable scenario: This solution is perfect for users who frequently handle large batches of data, prefer a graphical interface, or are not comfortable using formulas or code. It offers fast, bulk operation and reduces the risk of formula errors. The only requirement is that Kutools for Excel must be installed.
1. Select the range of cells where you want to add spaces before capital letters. Then go to the 'Kutools' menu > 'Text' > 'Add Text'. This opens the relevant options. 
2. In the "Add Text" dialog box, enter a space character in the "Text" box. Then, enable the "Only add to" option and select "Before uppercase letters" from the dropdown menu.
3. Click "OK" to apply the changes. Every capital letter in your selection will have a space inserted before it, as shown below:
Precautions:
- Check your selection carefully, as this operation applies directly to your data. It is a good practice to keep a backup before using batch editing features.
- This function is case-sensitive and only affects uppercase letters (A-Z).
Insert spaces before every capital letter with User Defined Function
For users who need greater flexibility or want a reusable solution for custom processing, creating a User Defined Function (UDF) in VBA allows you to implement precise logic for inserting spaces before uppercase letters. This approach keeps your spreadsheet dynamic, as the function behaves just like any other Excel formula and updates automatically.
Pros: Offers full customization and can be reused across different workbooks if you copy the code.
Cons: Requires basic familiarity with the VBA editor. UDF-based formulas may not work in all Excel environments, especially in Excel Online or with certain macro restrictions.
1. Open the worksheet containing the text strings to process.
2. Press ALT + F11 to open the Microsoft Visual Basic for Applications (VBA) editor.
3. In the VBA editor, click "Insert" > "Module". Copy and paste the following code into the newly created 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. Save your project and close the VBA editor. Go back to the worksheet. In a blank cell adjacent to your data (e.g., B2 if your data is in A2), enter the following formula and press Enter:
=addspaces(A2)Drag the cell's fill handle down to apply the UDF formula to the rest of your list. Each text string will display spaces inserted before every capital letter as designed.
Tips and troubleshooting:
- To keep the UDF available after reopening the file, save the workbook as a macro-enabled file (*.xlsm or *.xlsb).
- Changes in your text data will automatically refresh the UDF output.
- UDFs are not supported in Excel Online or some restricted macro environments.
Insert spaces before every capital letter with VBA code
If you prefer a solution that can process a large range of data at once and make permanent modifications directly to your worksheet, using a VBA macro is an excellent option. This method is best for advanced users or for one-time clean-up tasks, as it modifies the selected cells in place.
Benefits: Efficiently handles large datasets and does not require entering formulas in cells. You also have more control over which cells to process.
Considerations: Since this method alters your data directly, always make a backup before running the macro. Macros may be disabled by default in some workbooks for security reasons—enable macros as needed to proceed.
1. Press ALT + F11 to open the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and copy the following code into 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 Sub3. Press the "F5" key or click the "Run" button to execute the macro. A dialog will prompt you to choose the range you want to modify.
4. After selecting the range and clicking "OK", the macro will immediately insert spaces before all capital letters in the chosen cells.
Notes for troubleshooting:
- If the macro seems to do nothing, check if you have selected the intended range and have macros enabled in your Excel settings.
- This operation cannot be undone after execution. Use a backup if unsure.
- The macro is optimized for English uppercase letters (A-Z). Results may vary with non-standard characters or localized alphabets.
No matter which approach you choose, inserting spaces before every capital letter in Excel will greatly improve the legibility of your strings. Each method suits specific scenarios—formulas for dynamic calculations, Kutools for user-friendly automation, UDFs for reusability, and VBA code for powerful bulk operations. When using any of these techniques, double-check your data before applying in bulk, and keep a backup of original values to prevent accidental loss during processing. If you encounter errors, review your step-by-step process and ensure your Excel version supports the functions or code used. For more problem-solving strategies and tips in Excel, explore our extensive collection 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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in