Query for finding names and addresses in all uppercase

Louise Laberge · June 2016

Hi,

Does anyone know how to query records with names and addresses in all uppercase?

Louise

Alan French · June 2016

I don't know whether it's possible to do that in RE, but you could certainly export the entire list to Excel and identify them there very easily. Excel isn't normally case-sensitive when comparing text so you'll probably want to use the Exact function, which is.

=EXACT(A2,UPPER(A2)) will compare the contents of cell A2 to its upper case version and return TRUE if they are the same and FALSE if they are not, so you'll be interested in the ones that are true.

Once you've found the names that are incorrectly formatted, you can use the PROPER function to get them into the correct case. PROPER capitalises the first letter of every word, so you may find that it doesn't work correctly for names such as McDonald.

Daniel Noga 2 · June 2016

Alan French:

I don't know whether it's possible to do that in RE, but you could certainly export the entire list to Excel and identify them there very easily. Excel isn't normally case-sensitive when comparing text so you'll probably want to use the Exact function, which is.

=EXACT(A2,UPPER(A2)) will compare the contents of cell A2 to its upper case version and return TRUE if they are the same and FALSE if they are not, so you'll be interested in the ones that are true.

Once you've found the names that are incorrectly formatted, you can use the PROPER function to get them into the correct case. PROPER capitalises the first letter of every word, so you may find that it doesn't work correctly for names such as McDonald.

The PROPER function also capitalizes the first letter that comes after any character other than another letter, with a number of undesirable consequences where addresses are concerned. I've been doing a lot of cleanup like this lately, so I have a list in my head of stuff to run through and fix:

Numbered streets: PROPER will turn "1st" into "1St," 2nd into 2Nd, etc. I usually fix these with Find and Replace. Also keep in mind that you can't just run through the digits 0-9 and call it a day. Be mindful of "11th," "12th," and "13th."
VERY IMPORTANT if you're working with the "AddrLines" field instead of pulling the address lines separately (which is what happened for us when we created an import file of all of our addresses in order to standardise them all): For addresses with multiple lines, the AddrLines field separates the lines with "/n". This is case-sensitive, but PROPER will turn all of these into "/N," Once again, Find and Replace them all or your addresses will wind up a mess.
If you have corporations in your Organization records, you'll want to ensure that suffixes like LLP and LLC are properly capitalized, as PROPER will turn them into Llp and Llc. When I do this, I make sure to add a space in the Find and Replace fields before the suffix I want changed, and ensure that the "Match Case" box is checked. This is to avoid picking up things other than the suffixes, if that string of characters should happen to appear elsewhere.
If you like to keep articles, like "and," "or," "of," etc. in lowercase, do a Find and Replace for these. I add spaces before and after to ensure I don't pick things up by accident (like the name Anderson, for example)
P.O. Box addresses: If you keep to a standard of formatting these addresses "P.O. Box," you should be okay with PROPER. For us, we had inconsistency in entry in the past and some records were formatted as "PO Box." Keep in mind that PROPER will turn this into "Po Box."
For Organization Names, also be mindful of any acronyms that you want fully-capitalized and other, similar formatting issues.

There'll probably be one-off errors that you'll run across as you work--things you'd never predict--but the above are most common. I'll add one last time that you should be really, really careful using Find and Replace. I tried to stick to naming examples that are pretty safe to work with. Err on the side of caution if you're not sure, and do it manually. That being said, smart use of PROPER and Find and Replace can save you a lot of time in cleanup.

Joanne Felci 2 · June 2016

Alan French:

I don't know whether it's possible to do that in RE, but you could certainly export the entire list to Excel and identify them there very easily. Excel isn't normally case-sensitive when comparing text so you'll probably want to use the Exact function, which is.

=EXACT(A2,UPPER(A2)) will compare the contents of cell A2 to its upper case version and return TRUE if they are the same and FALSE if they are not, so you'll be interested in the ones that are true.

Once you've found the names that are incorrectly formatted, you can use the PROPER function to get them into the correct case. PROPER capitalises the first letter of every word, so you may find that it doesn't work correctly for names such as McDonald.

Alan - is there a way to find if only the first name is all caps - for example if there is a LISA Smith or something like that. I've tested the formula below and get a False result but I assume it is because the entire cell is not all uppercase

Alan French · June 2016

I'd either export the first and last names in separate fields, or if it's a lot of hassle to redo the export then you could use Excel's "Text to Columns" tool, using a space as the delimiter, to split the name up into multiple columns and then use the formula on each column. Exporting them separately is probably better if there's a chance that some constituents might have spaces anywhere other than between their first and last name (e.g. Anne Marie Smith) as Text To Columns will split those names across more than 2 cells which might make the data more of a pain to work with.

Joanne Felci 2 · June 2016

Thanks Alan!

Makes perfect sense...I just did not know if there was some other fancy, super smart, excel command

Query for finding names and addresses in all uppercase

Comments

Categories