Clean a string for Range Names – Regular Expressions


I am a big fan of using named  ranges in my VBA code. It makes referring to cells in formulas easy, and I can also helps in auditing the code. For instance, I can simply search for “RangeName” (including the quotes)  to see if that particular range name is being used in my code.

Excel has a “Create From Selection” functionality that allows the user to Name Ranges using the Top/Bottom row or Left/Right Column labels as names. When you have a label that does not qualify as a Name, it automatically cleans it up and names the range. Click here to learn more about the rules to naming a range.

There are two things I do not like about that feature,

  1. First, I don’t get to see the ‘Final’ name on the sheet.
  2. Second, it replaces all the spaces with underscores.

It is a good idea to have the Actual Named range visible in a sheet because it helps users to identify the range without having to dive into the formula bar to see those brightly coloured ‘boxes’. Replacing the spaces with underscores makes it incredibly difficult for the users to type in the range names into their formulae (and Code). Imagine having to reach for the ‘SHIFT’ button a billion times.

For this reason, I am a patron of the Capitalize-Each-Word-And-Ditch-The-Space rule. Capitalizing makes it easier for the user to identify each word. And since Excel names are not case sensitive, the user can just type in the entire name in lowercase while using them in formulas. Excel automatically converts them into the capitalized form.

Create Names

The quickest way to accomplish this is the remove all the “illegal” characters (by using the Like operator in a loop); convert the string into proper case (using the StrConv() function) and remove all the spaces (using the Replace() function). However, there is one tiny problem with this method that ticks me off. It converts some of the uppercase letters that the user intentionally typed in, to lowercase. For instance, if you planned to use the label ‘DOB’ for ‘Date of Birth’, the proper case method will change it to ‘Dob’. For some people with OCD (like me), this can be a deal breaker.

After I got introduced to Regular Expression for VBA, I realized I can fix this. Regular expression is a very powerful tool, but its implementation in VBA is not complete. RegExp for VBA does not have a few features like changing case for groups. I had to get creative, I introduced a space before every capital letter, converted it into proper case, and finally got rid of all the spaces. I am very proud for having thought of this by the way, do let me know if there is another way of accomplishing this.

The ‘CreateName’ function takes a string for an input and returns the “Cleaned-up” name:

'==========================================================================
'Function to clean up Labels to be used as Range Names
'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 02 February 2014
'Website : https://strugglingtoexcel.wordpress.com/
'==========================================================================
Function CreateName(ByVal WhichString As String) As Variant

Dim strTemp As String

'Keep only Letters, Numbers, Underscores and Spaces
strTemp = RegExpReplace(WhichString, "[^a-z^A-Z^0-9^\s^_]", _
                vbNullString, True)

'Enter Spaces before capitals
strTemp = RegExpReplace(strTemp, "([A-Z])", " $1", True)

'Insert space after underscores, to capitalize letters after
'underscore, when converted to proper case
'Remember that Excel's Proper Function is different from
'using StrConv with vbProperCase.
strTemp = Replace(strTemp, "_", "_ ")

'Convert to ProperCase
strTemp = StrConv(strTemp, vbProperCase)

'Remove all Spaces
strTemp = Replace(strTemp, " ", "")

'Use trim to remove any trailing spaces and output to cell
strTemp = Trim(strTemp)

'Names Cannot have a Number for the first character
If IsNumeric(Left(strTemp, 1)) Then strTemp = "_" & strTemp

'Names Cannot have more than 255 Characters
CreateName = Left(strTemp, 255)

End Function

In one of my previous posts, I published a simple function to simplify the use of Regular Expressions. It is required for the ‘CreateName’ function to work. The post also has information about how to add a reference to “Microsoft VBScript Regular Expressions 5.5” in your Visual Basics Project, which is needed for RegExp to work.

'==========================================================================
'Wrapper function to impliment the Replace method of the RegExp object
'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 08 December 2013
'Website : https://strugglingtoexcel.wordpress.com/
'==========================================================================
Function RegExpReplace(ByVal WhichString As String, _
                        ByVal Pattern As String, _
                        ByVal ReplaceWith As String, _
                        Optional ByVal IsGlobal As Boolean = True, _
                        Optional ByVal IsCaseSensitive As Boolean = True) _
                        As String
'Declaring the object
Dim objRegExp As Object
'Initializing an Instance
Set objRegExp = CreateObject("vbscript.regexp")
'Setting the Properties
objRegExp.Global = IsGlobal
objRegExp.Pattern = Pattern
objRegExp.IgnoreCase = Not IsCaseSensitive
'Execute the Replace Method
RegExpReplace = objRegExp.Replace(WhichString, ReplaceWith)

End Function

You may use a Cover macro to clean up all the cells in a selection:

'Cover Macro
Sub CoverMacro()

Dim rngEach As Range

For Each rngEach In Application.Selection
    rngEach.Value = CreateName(rngEach.Value)
Next rngEach

End Sub

Occasionally, when I create a spreadsheet application, I start of by typing out the Range names, so I can create a working prototype quickly. Later, when I am adding in the final touches, I need to add descriptions for each of the range names. Basically reverse the process. The following function can be used to create “Descriptions” from range names.

'==========================================================================
'Function to reverse what CreateName does.
'Author : Ejaz Ahmed - ejaz.ahmed.1989@gmail.com
'Date: 02 February 2014
'Website : https://strugglingtoexcel.wordpress.com/
'==========================================================================
Function LabelFromName(ByVal WhichString As String) As Variant

Dim strTemp As String

'Add a space between lower case and non lower case letters
strTemp = RegExpReplace(WhichString, "([a-z])([^a-z])", "$1 $2", True, True)

'Replace Underscores with spaces
strTemp = Replace(strTemp, "_", " ")

'Remove multiple spaces
strTemp = RegExpReplace(strTemp, "(\s)(\s+)", "$1", True)

'Use trim to remove any trailing spaces and output to cell
LabelFromName = Trim(strTemp)

End Function
Advertisements

3 thoughts on “Clean a string for Range Names – Regular Expressions

If you liked it, let me know. If you didn't make sure you let me know!

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s