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

sick

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.

Happy VBA coding!

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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.