Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to insert spaces before capital letters in excel?

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

Insert spaces before capital letters with VBA code

Insert space before every capital letters with Kutools for Excel

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


arrow blue right bubble 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


arrow blue right bubble 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


arrow blue right bubble 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 - Combines more than 300 Advanced Functions and Tools for Microsoft Excel

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 - Includes more than 300 handy Excel tools. Full feature free trial 60-day, 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.


arrow blue right bubbleRelated article:

How to add spaces after commas in Excel?


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Luca · 8 months ago
    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
    • To post as a guest, your comment is unpublished.
      kellytte · 4 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Excelsior · 2 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Paul · 2 years ago
    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
    • To post as a guest, your comment is unpublished.
      Jay · 2 years ago
      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