Troubleshooting Excel Issues with Case Sensitivity

As a user of Microsoft Excel, you may have encountered situations where you need to work with lists of street names in different notations. However, you might have noticed that Excel does not work case-sensitively when it comes to these notations. This can cause issues when filtering or creating pivot tables from the list, as the number of entries is reduced to a subset that is not accurate. In this blog post, we will explore how to force Excel to work in both cases sensitively and accurately.

Problem I – Filtering and Pivot Tables

When working with lists of street names, you may want to filter the list to only show entries with specific notations. However, if you try to filter the list using a case-sensitive notation, Excel will only show a subset of the original list. For example, if you have a list of 12 street names in different notations, and you try to filter the list to show only the entries with the notation “ORIGINALSCHREIBWEISE”, Excel will only show 9 out of the 12 entries.

Similarly, when creating a pivot table from the list, the result will also be reduced to only 9 entries. This is because Excel does not work case-sensitively and interprets the same string as identical, even if it is written in a different notation.

Error Interpretation

The error interpretation in both cases is that Excel does not work case-sensitively. It interprets the same string as identical, even if it is written in a different notation. This can lead to inaccuracies when filtering or creating pivot tables from the list.

Question

The question is how can we force Excel to work in both cases sensitively and accurately? Is there a way to make Excel recognize the same string as identical, regardless of the notation it is written in?

Solution

To force Excel to work in both cases sensitively and accurately, you can use the “PROPER” function to convert all strings to proper case before filtering or creating pivot tables. This will ensure that Excel recognizes the same string as identical, regardless of the notation it is written in.

Here’s an example of how to use the “PROPER” function to convert all strings to proper case:

1. Select the entire list of street names.

2. Press ALT + F11 to open the Visual Basic Editor.

3. In the Visual Basic Editor, click on “Developer” > “Code” > “Insert” > “Module”.

4. In the module, enter the following formula and press Enter:

=PROPER(ORIGINALSCHREIBWEISE)

This will convert all strings to proper case, so that Excel recognizes the same string as identical, regardless of the notation it is written in.

Conclusion

In this blog post, we have explored how to force Excel to work in both cases sensitively and accurately when working with lists of street names in different notations. By using the “PROPER” function to convert all strings to proper case, you can ensure that Excel recognizes the same string as identical, regardless of the notation it is written in. This can help you avoid inaccuracies when filtering or creating pivot tables from the list.