How Do I Quickly Insert Blank Space Between Upper Characters in a Pivot Field Title?


If you happen to be retrieving the data for your analysis by directly connecting Excel to a database, you might often encounter fields that have no spaces between their distinct words.

Whilst this is indeed best practice when writing your SQL query (having to deal with spaces in SQL aliases can be a very annoying matter), it is anything but user friendly for the people reading your spreadsheets.

Should you (or the analyst who had written the query) have stuck to the pattern of starting each new word in the field name with a capital letter, there’s a very sleek way, which you could use to apply some cosmetic tweaks on your subsequent pivot tables.

NB! The code below does assume that the pivot field names are only composed of two distinct words, each of which starts with a capital letter.

How does it work?

The VBA-routine will loop through all the value fields in all the pivot tables of your spreadsheet and find the second capital letter in the pivot field name. Then it will replace the name of the pivot field with one that has a space right before the second capital letter in the string.

Sub InsertBlankSpacesBetweenUpperCharactersInName()
Dim Wks As Worksheet
Dim PT As PivotTable
Dim PF As PivotField
Dim mStr As String
Dim i As Integer
Dim FindUpper As Integer
'written by Angelina Teneva, Feb 2017; assumes the characters has only two upper characters
For Each Wks In ActiveWorkbook.Worksheets
For Each PT In Wks.PivotTables
On Error Resume Next
For Each PF In PT.DataFields
mStr = PF.Caption
For i = 2 To Len(mStr)
If Mid(mStr, i, 1) Like "[A-Z]" Then
FindUpper = i
PF.Caption = Left(mStr, FindUpper - 1) & Chr(32) & _
Right(mStr, Len(mStr) - FindUpper + 1)
Exit For
End If
Next i
Next PF
Next PT
Next Wks
End Sub

Happy VBA coding!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.