One of the problems associated with using Excel is that the vast majority of functions within the software package are case-insensitive including things like pivot tables. However, there are a few exceptions which include things such as FIND, EXACT, and MATCH. So is it possible to change the case sensitivity of Excel?

It is not possible to change the case sensitivity of the functions within Excel, however, it is possible to develop workarounds that can overcome cases where case sensitivity is required. There are two possible solutions that can be employed. The first method requires the use of conditional formulas in combination with those functions which have case sensitivity listed above.  

However, in cases where case sensitivity is a problem in pivot tables the only option is to alter the underlying data to ensure that there are unique data tags that allow the pivot table to differentiate between data with different case settings. To do this on a mass basis requires the use of VBA code. 

For those unfamiliar with VBA code, we have developed within this blog some code that is easier to use and requires only one or two changes to suit your particular spreadsheet.  This code is provided at the end of the article.

Using Excel Functions To Detect Differences In Case

To detect differences in the case of text the easiest function to use at the ones mentioned above is the EXACT function because it returns a true and false when comparing two pieces of text.  this means that it is easier to place it into an if statement which will allow you to take action based on these differences. An example of this is provided below;

=IF(EXACT(A1, A2), “Is True”, “Is False”)

However, the EXACT function is really only suitable in cases where you are trying to match the entire value of a cell. If you need to find text within a string then the FIND  function is which is required. The structure of the FIND function is provided below;

FIND(find_text, within_text, [start_num])

The FIND function syntax has the following arguments:

Find_text: Is required and is the text you want to find.

Within_text: Is required and contains the text you want to find.

Start_num: Is an optional argument that specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

If the text is not present the function will produce an error  therefore creating an IF statement using this particular function will also require the use of the ISERROR function within Excel an example of an IF statement using FIND  is provided below;

=IF(ISERROR(FIND(“text I want find”, A1)), “No Match”, “Matched”)

The MATCH function is the function that is most useful in cases where you want to use a VLOOKUP that is case-sensitive. As the VLOOKUP is not case-sensitive you can use a combination of the INDEX function and the MATCH function to create an equivalent function that is case-sensitive. 

The MATCH function works by searching array values to return the row number that the match is in. The structure of the MATCH function is as follows; 

MATCH(lookup_value, lookup_array, [match_type])

The MATCH function syntax has the following arguments:

Lookup_value: Is the value that you want to match in lookup_array. 

lookup_array: Is the range of cells being searched.

Match_type: This is an optional argument that can be -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.

1 = Highest Value matching the lookup value in the array

0 = Matches the first value that it finds

-1 = Matches the Lowest Value

The MATCH  functions can be used in combination with the INDEX function to produce a similar output to a vlookup. The structure of the INDEX function is as follows;

INDEX(array, row_num, [column_num])

The array form of the INDEX function has the following arguments:

Array: Is the range of cells or an array.

row_num: Selects the row in the array from which to return a value. 

column_num: Selects the column in an array from which to return a value.

To use INDEX and MATCH together the MATCH function needs to replace the row_num argument in the INDEX function. An example of this is provided in the video below.

Modifying Data For Pivot Tables To Make Them Case-Sensitive

As discussed earlier in the article pivot tables are case insensitive which means that if we have data labels with different cases the pivot table will not be able to differentiate between the data labels. For example, if we have our users’ names listed three times as follows in the data it will appear only once and combine any values associated with those data labels.

Peter Wright

peter wright

PETER WRIGHT

The only way to differentiate between these values is to add data tags to the information that is going to be used in a pivot table. As capitalization of letters can vary between uppercase,  lowercase, and proper case, or some other random variant it is important to develop a unique data tag for each of these possibilities.

To do this the code below has been designed to add numeric values at the end of the text data based on the position of the capitalized letters within the text. Ie If the capitalized letter is in the first position within the text a 1 will be added to the text. An example of this is provided below;

As mentioned above the code has been designed specifically for those people that have no experience with VBA code I need to just get something to work because there is only one modification required for the first piece of code which will add data tags to a single row of text.

The second piece of code is designed  2 add data tags to an entire spreadsheet if you have a block of day that you want to convert quickly and easily.  it’s important to note that this code will only affect data with letters in it.

How To Modify The Code To Suit Your Spreadsheet

To make it extremely easy to modify the code I have highlighted the things that you need to change within the code in bold to modify to suit your own spreadsheet. In the case of the code that converts a single row of data, you need to specify the name of the sheet on which you want to run the code and a column letter. In the case of the code that converts the entire sheet you only need to specify the name of the sheet.

The video below has instructions on precisely how to do this if the written instructions here are unclear. Additionally, this video contains an explanation of precisely how the code works if you are really keen to know precisely what it is doing.

To add the code to your spreadsheet you need to access the VBA editor, this can be done by pressing ALT + F8. The pop-up box shown below will appear once these buttons are pressed at which point you will be able to go into the VBA editor either by selecting the name of an existing macro and press the step into button. Alternatively, if there are no current macros in your file you will need to click the create button.

To create a place to put the code you will need to create a module which can be  done by selecting insert and then module.

Once this is done an area will appear where you can place the code into the editor and modify the one or two lines in bold below then close the VBA editor.

To run the macro you need to access the VBA editor in the same way as you did before by pressing ALT + F8 and selecting the macros name and pushing the run button.

Sub Convert_A_Single_Row()

'Declares Variables
Dim SheetName As String
Dim i As Integer
Dim UpperCase As String
Dim Row As Integer
Dim LastRow As Integer
Dim ColumnName As String

'sets sheet name and column
SheetName = "Sheet2"
ColumnName = "B"

'finds last row of data
LastRow = Worksheets(SheetName).UsedRange.Rows.Count

'sets up a loop to step through each row with the column
For Row = 1 To LastRow Step 1

    'sets up a loop to step through each character in the cell
    For i = 1 To Len(Worksheets(SheetName).Cells(Row, ColumnName))
        
        'tests if the character is a letter and uppercase
        Select Case Asc(Mid(Worksheets(SheetName).Cells(Row, ColumnName), i, 1))
            
            Case 65 To 90
                'records the position of the character
                UpperCase = UpperCase & i
        
        End Select
    
    Next i
    
    'adds the positions of the uppercase characters to the text
    Worksheets(SheetName).Cells(Row, ColumnName) = Worksheets(SheetName).Cells(Row, ColumnName) & UpperCase
    
    'resets the the UpperCase variable to empty ready for the next cell
    UpperCase = ""

Next Row

End Sub

Sub Convert_An_Entiresheet()

'Declares Variables
Dim SheetName As String
Dim i As Integer
Dim UpperCase As String
Dim Row As Integer
Dim Column As Integer
Dim LastRow As Integer
Dim ColumnRow As Integer
Dim ColumnName As String

'sets sheet name
SheetName = "Sheet2"


'finds last row & column of data
LastRow = Worksheets(SheetName).UsedRange.Rows.Count
ColumnRow = Worksheets(SheetName).UsedRange.Rows.Count

'sets up a loop to step through each row with the column
For Row = 1 To LastRow Step 1

    For Column = 1 To ColumnRow Step 1
        'sets up a loop to step through each character in the cell
        For i = 1 To Len(Worksheets(SheetName).Cells(Row, Column))
        
            'tests if the character is a letter and uppercase
            Select Case Asc(Mid(Worksheets(SheetName).Cells(Row, Column), i, 1))
            
                Case 65 To 90
                    'records the position of the character
                    UpperCase = UpperCase & i
        
                End Select
    
        Next i
    
        'adds the positions of the uppercase characters to the text
        Worksheets(SheetName).Cells(Row, Column) = Worksheets(SheetName).Cells(Row, Column) & UpperCase
    
        'resets the the UpperCase variable to empty ready for the next cell
        UpperCase = ""
    
    Next Column
    
Next Row

End Sub

Relevant Articles

What Is Opposite Of Concatenate? How Do I Unconcatenate Text?

What Does #### Mean In Excel? And How To Fix The Problem

Is Learning Excel Worth It? Will It Make A Difference?

Why Is The Vlookup Returning #N/A When Value Exists? What Is The Problem And How To Fix It?