PowerGrid Formulas

The following formulas are available in PowerGrid and can be applied by right-clicking on a cell, group of cell, or an entire column.

Normalize US Address: Standardizes a street address to match the USPS preferred format.

Address line 1 and address line 2 are returned on one line (this is the USPS preferred format).

Examples:
Input: 7th Floor, 162 Washington St.
Result: 162 Washington St Fl 7

Input: One North Main Street, Suite 100
Result: 1 N Main St Ste 100

This DOES NOT verify the street address, just attempts to normalize the data to the USPS standards.  Results are returned in proper case vs. upper case.

Results will vary based on the validity of the supplied address. For example, street addresses that contain building names (i.e. 123 Main St Suite 100 One Prudential Tower) WILL RETURN UNWANTED RESULTS.

Propercase Name: Proper Case a "Name" field, typically a Company/Account Name, or First and Last Name. Instead of just upper casing the first letter of each word, will apply logic to handle common prefixes (e.g. Mc and Mac), abbreviations, and allows for user overrides to always punctuate a particular string in a preferred manner.

• ONLY attempts to proper case when the input data is all in upper or lower case
  • If the input data is already proper cased (combination of upper and lower case, e.g. eBay) changes will NOT be made
• Logic regarding the prefix “Mac” (e.g. does not capitalize the first letter after “Mac” if the word is 5 or less letters)
• Logic to identify abbreviations
  • When there is no punctuation between the letters, and the word contains NO VOWELS, the word is assumed to be an abbreviation and all letters will be uppercased
    • For Example:  BCBS, GM, JFK etc.
• ProperCaseOverrides.txt file available in DemandToolsData\ReplaceList directory for user customization
  • Allows the user to add overrides for any words the formula is not proper casing in a preferred manner
  • Abbreviations containing a vowel should be added here as the standard logic will only assume a word is an abbreviation if it does not contain a vowel.
  • Words in this list will be returned in the format specified
    • Examples applying to Company/Account Names:
      • IBM, AIG, AAA, USA, eBay, salesforce.com
      • Examples applying to First/Last Names:
        • AJ, EJ, PhD, CPA
  • This list is pre-populated but can be updated as needed
    • Click HERE for more information on this file

Note: This file is loaded upon login, therefore if changes are made they will not be recognized until the user logs out and back in to DemandTools.

Examples:

Input: MARY                                          Input: JJ ENTERPRISES
Result: Mary                                           Result: JJ Enterprises

Input: mcdonald's corp.                         Input: Validity, Inc.
Result: McDonald’s Corp.                       Result: Validity, Inc. (already proper-cased so NO changes made)

Input: HEWLETT PACKARD                     Input: EBAY
Result: Hewlett Packard                          Result: eBay
                                                                            (cases eBay since this value is in the ProperCaseOverrides.txt file)

Note: The following words WILL NOT be capitalized: and, at, by, for, of, or, to, it.

Propercase Name IC: Same as ProperCaseName EXCEPT proper casing all data REGARDLESS of existing case.

Examples:

Input: Mary
Result: Mary

Input: mary
Result: Mary

Input: JAB Consulting
Result: Jab Consulting (assumes JAB is NOT in the ProperCaseOverrides list)

When using this version of Propercase it is especially important to review the ProperCaseOverrides list and customize as needed.

Upper Case: Takes a field and upper cases letters.

Example:
Input: ma
Result: MA

Lower Case: Takes a field and lower cases letters

Example:
Input: MA
Result: ma

Country Match 2Char: Converts country to ISO 2 character name
Example:
Input: Germany
Result: DE

Country Match 3Char: Converts country to ISO 3 character name
Example:
Input: United States
Result: USA

Country Match Long: Converts country to long form
Example:
Input value: CA
Result: Canada

Note: United Kingdom or UK will be changed to GB or GBR, respective to the formula in use as this is the ISO format.

NA Phone Fix:  Takes a phone number and formats it to match the format as entered in Salesforce user interface, e.g. (XXX) XXX-XXXX. Will also standardize an abbreviation for extension to x.

Examples:

Input: 212-555-1212
Result: (212) 555-1212

Input: +1 212.555.1212
Result: (212) 555-1212

Input: 2125551212 ext155
Result: (212) 555-1212 x155

This is a North American phone fix, DO NOT use with international phone numbers. If less than 10 characters returned after stripping alpha characters and punctuation, DemandTools will return the original input data.

Zip Code Clean: Attempts to reformat US zip codes adding a leading zero if needed.  The primary purpose is to fix zip codes where the leading zero was truncated on import.

Example:
Input: 2043
Result: 02043

This is JUST for US zip codes and specifically those in states that typically have a leading zero.  May cause unwanted results with non US zip codes.

State Match Short: Updates all US states & Canadian provinces to the ISO two character abbreviation

Example:
Input: Florida
Result: FL

Input: Ma
Result: MA

State Match Long: Updates all US states & Canadian provinces to the long form 
Ability to customize to accommodate international states. Click HERE for more details.

Example: 
Input: OH
Result: Ohio

Note: The State formulas will also convert common abbreviations for states to the appropriate long or short form (e.g. Conn -> Connecticut, CT, Mass -> Massachusetts, MA, Cal -> California, CA, Miss -> Mississippi, MS).