{"id":1245,"date":"2020-01-22T10:53:29","date_gmt":"2020-01-22T18:53:29","guid":{"rendered":"http:\/\/bobbacon.net\/blog\/?p=1245"},"modified":"2020-11-27T21:36:33","modified_gmt":"2020-11-28T05:36:33","slug":"ever-wonder-why-acronyms-look-like-this-in-your-database","status":"publish","type":"post","link":"https:\/\/bobbacon.net\/blog\/archives\/1245","title":{"rendered":"Ever Wonder Why the Capitalization of Your Acronyms is Wrong in Your Database? (and how to fix them… )"},"content":{"rendered":"\n
\"\"
Case insensitive representation of MTV<\/figcaption><\/figure><\/div>\n\n\n\n

Maybe I\u2019m a little OCD but when I look at a database of account names and find capitalization inconsistencies it leads me to think that those people responsible for data quality aren\u2019t doing a very good job.  Users who add new accounts to the database are given implied permission to be less concerned about the quality of the data they add.  Law enforcement officers call this phenomenon the \u201cbroken window theory<\/a>.\u201d  Questionable data quality encourages more questionable data quality.  If users can point to this as the acceptable level of quality, they can call the accuracy of the entire database into question and before long sales reps use this as one of their excuses for not making their number!<\/p>\n\n\n\n

We\u2019ve all seen account names where the abbreviation of their names are mixed case, (e.g. Mtv, Rbs, Cvs, Nbc, etc.). Why are these upper and lower case errors so common?  The answer goes back decades when computers only used upper case. But, users needed upper and lower case as word processing merged with everyday computing.  Someone developed a function that converted the upper case names to a mix of upper and lower case based on the first letter of each word in the text string.  In Excel this function is called PROPER and it will convert MTV NETWORKS COMPANY to Mtv Networks Company or simply MTV to Mtv.  I believe widespread use of PROPER and functions like it are the primary reason you see case errors in account names today.  It is also responsible for incorrect capitalization of prepositions as in \u201cCity Of Miami\u201d but that issue is simple to fix with the Find and Replace function in Excel. Just make sure you put a space before and after the “Of” when searching or bad things will happen…<\/p>\n\n\n\n

Some organizations simply address the acronym challenge by\nputting a space between the letters, e.g. M T V instead of MTV but I\u2019m not a\nfan of that compromise because OCD.<\/p>\n\n\n\n

Excel actually has a number of text handling functions which we can use below in a couple of formulae to identify and repair the case errors in your database.\u00a0 Before we get into the formulae let me set expectations properly.\u00a0 <\/p>\n\n\n\n

There is no simple formulaic solution that corrects capitalization of acronyms without human participation.  You will have to manually review about 20% of your accounts because they have two, three or four letters in the first word of their name.  It takes human intervention to determine if those two, three or four letters are a name, a word or should be an acronym.  The formula I use makes this manual job a whole lot easier assuming you have basic administrator ETL skills with your data set.<\/p>\n\n\n\n

The first step is to identify which account names in your database need that human intervention.  Extract the account names and a corresponding account ID number (so you can upload the corrected name back into your database) into an Excel worksheet in columns B (name) and C (ID).  Label column A “Qualified”. Copy and paste the below formula into cell A2 and fill down to the bottom of the list of account names.<\/p>\n\n\n\n

=IF(AND(IFERROR(FIND(” “,B2,1)-1,LEN(B2))>=2,IFERROR(FIND(” “,B2,1)-1,LEN(B2))<=4),”Check”,””)<\/strong><\/p>\n\n\n\n

The formula will resolve to \u201cCheck\u201d for those account names having two, three or four characters in the first word of their name.  Now sort the sheet by column A and column B.  This will group together all those account names needing to be checked.  I\u2019m suggesting you sort by both the formula result and the account name since this is a good time to also look for account name duplicates. Plus having the same words sorted together will allow you to skip over those names that don\u2019t need to be changed much faster.<\/p>\n\n\n\n

Now label column D \u201cCorrected Account Name\u201d and column E \u201cFlag\u201d.  Copy this next formula into cell D2 and fill down to the bottom of the list of names where the first formula resolved to “Check”.<\/p>\n\n\n\n

=IF(E2=””,B2,UPPER(LEFT(B2,IFERROR(FIND(” “,B2,1)-1,LEN(B2))))&MID(B2,IFERROR(FIND(” “,B2,1),LEN(B2)),LEN(B2)-IFERROR(FIND(” “,B2,1)-1,LEN(B2))))<\/strong><\/p>\n\n\n\n

Begin your manual review of the list and when you find an acronym that should be capitalized in the account name enter any character (e.g. \u201cx\u201d) in the corresponding row in column E and the name will be corrected in column D.  When you have completed the review, select column D and copy > paste special > values to convert the account names into text so they can be uploaded to your database.<\/p>\n\n\n\n

So, while this method has worked for me over the years I\u2019m interested if any of my Sales or Marketing Ops readers have a more efficient approach.  Please enlighten us below and thanks for reading!<\/p>\n","protected":false},"excerpt":{"rendered":"

Maybe I\u2019m a little OCD but when I look at a database of account names and find capitalization inconsistencies it leads me to think that those people responsible for data quality aren\u2019t doing a very good job.  Users who add … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_s2mail":"no","footnotes":""},"categories":[4],"tags":[112,26,68,67,110,111,35,106,69,78],"class_list":["post-1245","post","type-post","status-publish","format-standard","hentry","category-sales_ops","tag-acronyms","tag-bob-bacon","tag-data-quality","tag-dq","tag-etl","tag-proper","tag-sales-ops","tag-salesforce","tag-sfdc","tag-sfdc-cleanup"],"_links":{"self":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/1245","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/comments?post=1245"}],"version-history":[{"count":31,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/1245\/revisions"}],"predecessor-version":[{"id":1363,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/posts\/1245\/revisions\/1363"}],"wp:attachment":[{"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/media?parent=1245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/categories?post=1245"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bobbacon.net\/blog\/wp-json\/wp\/v2\/tags?post=1245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}