Finding ALL CAPS in large dataset

**renee705** · 07-30-2012, 01:34 PM

Is there a way to Find and highlight ALL CAPS within a large dataset and make them proper?

I don't want to use the Proper function on the entire dataset since I am working with Company Names. I have a mixture of proper and CAPS within the dataset. I want to keep the formatted Companies as is since they were corrected such as PJM Interconnections LLC and proper would undo the corrections and change it to Pjm Interconnections Llc which I don't want.

Example:
Dish Network
Western State Hospital
The City of New York
BFG KALAMAZOO LLC
5th Food Group
PJM Interconnections LLC
King Architectural Metals
Alliant Techsystems Inc
RAYBESTOS POWERTRAIN, LLC
RAYBESTOS POWERTRAIN, LLC
Wienerschnitzel
Ichor Systems, Inc

I want to be able to highlight the ALL CAPS so that I can do a Proper function on just those Companies.
Does anyone know how to do this? I am working with a 500K rows of data

**shg** · 07-30-2012, 01:44 PM

Please Login or Register  to view this content.

**rvasquez** · 07-30-2012, 01:59 PM

Hello there,

I have provided two options below.

The first finds and highlights all cells that are uppercase red.

The second finds the cell that's uppercase then provides you with a message box asking if you'd like to proper case it. If you select yes then the macro proper cases the cell and then formats any cells that end in llc to LLC. I think you might like option 2 so you might want to give that a try first.

These options assume that your data is in Column A
If it is not the please change all A's to the correct column letter before attempting to run the code.
Option 1

Please Login or Register  to view this content.

Option 2

Please Login or Register  to view this content.

To insert one of these codes

1.Press Alt+F8 on your keyboard
2.Clear the macro name box and type LocateCells in the blank box provided
3.Select the Create option
4.In between the Sub LocateCells() and End Sub copy and paste one of the options I have provided above.
5.Exit out of the Visual Basic Window
6.Press Alt+F8 again and this time select the LocateCells macro
7.Select Run

Let me know how these work for you!