Skip to main content

How to insert spaces before capital letters in excel?

Author: Xiaoyang Last Modified: 2020-09-10

Supposing you have a list of text strings which all spaces between the words are removed accidentally, like this: InsertBlankRowsBetweenData, and now you want to add the spaces before each letter with capital letters to separate the words as Insert Blank Rows Between Data. How could you add spaces in front of the capital letters quickly instead of type spaces one by one in Excel?


Insert spaces before capital letters with User Defined Function

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:
doc-add-spaces-before-uppercase-1

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.
doc-add-spaces-before-uppercase-1

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.


ad remove space 1

Insert spaces before capital letters with VBA code

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.
doc-add-spaces-before-uppercase-1

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


Insert space before every capital letters with Kutools for Excel

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

Kutools for Excel - Packed with over 300 essential tools for Excel. Enjoy a full-featured 30-day FREE trial with no credit card required! Download now!

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:
doc add spaces before uppercase 7

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy a full-featured 30-day FREE trial with no credit card required! Get It Now

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.


Related article:

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

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...

Description


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!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This function handles two problems that the given solution doesn't cover:
1. non-English text (with diacritical marks)
2. successive capitals that should not have spaces after them

` Private Function AddSpaces(sText As String)As String
' Inserts a space immediately before a capital letter, except when successive characters are each capitalized.
' Last Updated: 2022-07-04
'
Dim sRet As String, i As Integer, sChar As String, bPrevWasUCase As Boolean

sRet = Left(sText, 1)
For i = 2 To Len(sText)
sChar = Mid(sText, i, 1)
If sChar = UCase(sChar) And Not bPrevWasUCase Then
sRet = sRet & " " & sChar
bPrevWasUCase = True
Else
sRet = sRet & sChar
bPrevWasUCase = False
End If
Next i

AddSpaces= sRet
End Function`
This comment was minimized by the moderator on the site
Hello friend,

Thanks for your share. I tried your VBA code, but it doesn't work. Our VBA code can handle the non-english text. I have test the french text and spaces are successfully inserted before the capital letters.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Hello

First thanks a lot for your code example and the explanations. It works very well for my case.

I just have 1 Problem, some of the Strings contain words like URL, which should not be seperated.
Do you see a way to except for example "URL" from adding spaces?

Would be very nice if you can help. I tried already a while, but i dont know how to solve this..


Best regards
luca
This comment was minimized by the moderator on the site
hi wanna ask you if you found solution for you case, if you found solution please provide me with that
This comment was minimized by the moderator on the site
Hi,
Thanks for your comment and advice. I have sent your suggestion to the Kutools project team, I’m sure they’ll improve it soon.
This comment was minimized by the moderator on the site
Found a solution?
This comment was minimized by the moderator on the site
Hi there, your code works fantastic. Do you know how to adapt code 20140723 to work on multiple sheets? I would like to add spaces between capital letters throughout the entire workbook. Thanks!
This comment was minimized by the moderator on the site
i need a help in excel i want to find Uppercase in a cell. for example: Sagar Paul MBA 16:04 i want MBA to be highlighted
This comment was minimized by the moderator on the site
I hope you can see it and give it a trial. Sub Test() Dim Rg As Range, xCell As Range Dim i As Long Dim xChar As String Set Rg = Application.Selection For Each xCell In Rg For i = 1 To xCell.Characters.Count xChar = xCell.Characters(i, 1).Text If Asc(xChar) > 64 And Asc(xChar) < 91 Then xCell.Characters(i, 1).Font.Color = vbRed End If Next Next End Sub
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations