Upper / Lower case

Question

I need some help. Aim - If a user enters the first name in caps lock, I want a formula to keep the first letter as a capital and then the rest being lower case. (However there are some additional criteria which I need to add)

I have created the following formula (see below) which so far works. And does the following; daniel = Daniel DANIEL = Daniel

UPPER( LEFT(FirstName , 1) ) & LOWER( MID(FirstName , 2, LEN(FirstName ) ) )

However when testing I noticed that if you have a middle name then this also gets changes to lower case.

Desired results; DANIEL JOHN = Daniel John DANIEL = Daniel daniel = Daniel MCCLOUD = McCloud DANIEL-JOHN = Daniel-John

Object: Contact Field: First Name

EDIT

I have changed the formula to:

IF(FIND(" ",FirstName)>0, UPPER(LEFT(FirstName,1))+ MID(LOWER(FirstName), 2, FIND(" ",FirstName)-1)+ " " + UPPER(MID(FirstName, FIND(" ",FirstName)+1, 1))+ LOWER(MID(FirstName, FIND(" ",FirstName)+2, Len(FirstName)-FIND(" ",FirstName)+2)) ,UPPER(LEFT(FirstName,1))&Mid(LOWER(FirstName), 2, Len(FirstName)-1)) 

This resolves all the issues except for the McCloud scenario

Answer 1

Sadly, there is no "PROPER" or "PROPERCASE" function available in Salesforce formulas. The issue has been raised in the Salesforce Success ideas area several times.

Because of the lack of temporary variables, this kind of thing is annoyingly hard to do with Salesforce formulas. There's the beginning of an answer here. That will handle a name with three words in it (eg "Jimmy Joe Bob"). The final version of the formula on that page is:

IF (
  FIND(" ", City ,1)=0,
  UPPER(LEFT(City ,1))&LOWER(MID(City,2,LEN(City)-1)),
  IF(
    FIND(" ",MID(City ,FIND(" ",City ,1)+1,LEN(City)-FIND(" ",City,1)))=0,
    UPPER(LEFT(City ,1))&LOWER(MID(City ,2,FIND(" ",City ,1)-1))&UPPER(MID(City ,FIND(" ",City ,1)+1,1))&LOWER(MID(City ,FIND(" ",City ,1)+2,LEN(City)-1)),
    UPPER(LEFT(City ,1))&LOWER(MID(City ,2,FIND(" ",City ,1)-1))&UPPER(MID(City ,FIND(" ",City ,1)+1,1))&LOWER(MID(City ,FIND(" ",City ,1)+2,FIND(" ",MID(City ,FIND(" ",City ,1)+1,LEN(City )-FIND(" ",City ,1)))-1))&
    UPPER(MID(City ,FIND(" ",MID(City ,FIND(" ",City ,1)+1,LEN(City)-FIND(" ",City ,1)))+FIND(" ",City ,1)+1,1))&LOWER(MID(City ,FIND(" ",MID(City ,FIND(" ",City ,1)+1,LEN(City )-FIND(" ",City ,1)))+FIND(" ",City ,1)+2,LEN(City)-1))
  )
)

However, that only handles names with spaces. A name with a combination of spaces and hyphens is going to get more challenging.

"McCloud" and similar might possibly be done by replacing "mcc" with "McC" (unless the string "mcc" appears in the middle of any names). I have to wonder though. How many people with hyphenated Scottish middle names are there?

Answer 2

As Jagular says, doing this in formula fields is hard when you have special cases - I've included an apex utility class I wrote that I used in a trigger. It is not perfect but handles most normal Western European names - and includes a special feature to deal with company names. If you look at the test method, you can see what it covers

    private static testmethod void testInitialCaps() {
        System.assertEquals(null,           initialCaps(null,null));
        System.assertEquals('',             initialCaps(String.valueOf(''),null));
        System.assertEquals(' ',            initialCaps(String.valueOf(' '),null));
        System.assertEquals('Joe',          initialCaps(String.valueOf('joe'),null));
        System.assertEquals('AbCDE',        initialCaps(String.valueOf('AbCDE'),null));
        System.assertEquals('A.',           initialCaps(String.valueOf('A.'),null));
        System.assertEquals('A',            initialCaps(String.valueOf('A'),null));
        System.assertEquals('A',            initialCaps(String.valueOf('a'),null));
        System.assertEquals('3456',         initialCaps(String.valueOf('3456'),null));
        System.assertEquals('Abcde',        initialCaps(String.valueOf('ABCDE'),null));
        System.assertEquals('Abcde Ghijk',  initialCaps(String.valueOf('ABCDE GHIJK'),null));
        System.assertEquals('Marie-Helene', initialCaps(String.valueOf('MARIE-HELENE'),null));
        System.assertEquals('O\'Leary',     initialCaps(String.valueOf('O\'LEARY'),null));
        System.assertEquals('Joe O\'Leary',         initialCaps(String.valueOf('JOE O\'LEARY'),null));
        System.assertEquals('Joe O\'Leary III',     initialCaps(String.valueOf('JOE O\'LEARY III'),null));
        System.assertEquals('Frédéric',             initialCaps(String.valueOf('FRÉDÉRIC'),null));
        System.assertEquals('Gérard Le Bihan',      initialCaps(String.valueOf('GÉRARD LE BIHAN'),null));
        System.assertEquals('Old McDonald',         initialCaps(String.valueOf('OLD MCDONALD'),null));
        System.assertEquals('Gerd von Rundstedt',   initialCaps(String.valueOf('GERD VON RUNDSTEDT'),null));
        System.assertEquals('Joe Jr. Blow',         initialCaps(String.valueOf('JOE JR. BLOW'),null));
        System.assertEquals('Hp',                   initialCaps(String.valueOf('HP'),null));
        System.assertEquals('HP',                   initialCaps(String.valueOf('HP'),'companynamerules'));
    }


    //  ----------------------------------------
    //  initialCaps
    //  ----------------------------------------
    public static String initialCaps(String s, String specialHandling) {
        String res;
        //  Detect if all caps or all lowercase; if not, leave alone
        //  Detect if single uppercase char, leave alone

        //  Rules - companynamerules : If 3 chars or fewer and all CAPS, leave alone -- NFL, MLB, BNP, GM, HP

        Boolean applyCompanyNameRules       = specialhandling == 'companynamerules' ? true : false;

        if (s == null || s.length() == 0 || s == ' ') {
            res = s;
            return res;
        }
        String allLowerCaseCand = s.toLowerCase();
        String allUpperCaseCand = s.toUpperCase();

        if (allLowerCaseCand.equals(s) ||                                                       // equals() is case sensitive whereas == is no
            (allUpperCaseCand.equals(s) && s.length() > 1 && !applyCompanyNameRules)) {}        // It is either all upper (>1 char) or all lower (1+ char)
        else
        if (allUpperCaseCand.equals(s) && s.length() <= 3 && applyCompanyNameRules) {
            res = s;
            return s;
        }
        else {
            res = s;                                                // leave as is and return
            return s;   
        }



        //  Take s and split into tokens separated by spaces
        List <String> wordList  = s.split('\\p{Space}');
        res = '';               // reset 
        // For each word, initial cap it, being aware of hyphens and apostrophes

        for (Integer i = 0; i < wordList.size(); i++) {
            String word = wordList[i];
            res = res + (i == 0 ? '' : ' ');
            if (word.indexOf('-') != -1) {
                String [] tokenList = word.split('\\-');
                for (Integer j = 0; j < tokenList.size(); j++) {
                    String token = tokenList[j];
                    res = res +  initCapToken(token) + (j < tokenList.size() - 1 ? '-' : '');
                }
            }
            else 
            if (word.indexOf('\'') != -1) {
                String [] tokenList = word.split('\'');
                for (Integer j = 0; j < tokenList.size(); j++) {
                    String token = tokenList[j];
                    res = res +  initCapToken(token) + (j < tokenList.size() - 1 ? '\'' : '');
                }
            }
            else
                res = res + initCapToken(word);
        }
        res = res.trim();
        return res;
    } 

        // special case handling 
        //  lastname
        //  Mc, Mac, III, IV, van, von
    private static String initCapToken (String token) {
        Set<String> allowedAllCapSet    = new Set<String> {'III','IV'};
        if (allowedAllCapSet.contains(token))
            return token;
        if (token.startsWith('MC') && token.length() > 2)
            return 'Mc' + token.substring(2,3) + token.substring(3,token.length()).toLowerCase();
        if (token.equals('VON'))
            return 'von';
        //  ..add other cases here like van, da, di, de della, do, du, la, le


        return token.substring(0,1).toUpperCase() + token.substring(1,token.length()).toLowerCase();
    }

Answer 3

I have this to handle upper case for the first letters of the first and last names, and also accounts for - in the string. I need to add a consideration for ' like O'Leary in the example above, which currently changes changed to O'leary with the below formula in Flow. I'm not experienced with formula writing so I'm not sure how to insert it into the formula and hoping to bypass the Apex route.

IF ( FIND(" ", SUBSTITUTE(FirstName, "-", " ") ,1)=0, UPPER(LEFT(FirstName ,1))& LOWER(MID(FirstName,2,LEN(FirstName)-1)), IF( FIND(" ",MID(SUBSTITUTE(FirstName, "-", " ") ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,LEN(FirstName)-FIND(" ",SUBSTITUTE(FirstName, "-", " "),1)))=0, UPPER(LEFT(FirstName ,1))&LOWER(MID(FirstName ,2,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)-1))&UPPER(MID(FirstName ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,1))&LOWER(MID(FirstName ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+2,LEN(FirstName)-1)), UPPER(LEFT(FirstName ,1))&LOWER(MID(FirstName ,2,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)-1))&UPPER(MID(FirstName ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,1))&LOWER(MID(FirstName ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+2,FIND(" ",MID(SUBSTITUTE(FirstName, "-", " ") ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,LEN(FirstName )-FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)))-1))& UPPER(MID(FirstName ,FIND(" ",MID(SUBSTITUTE(FirstName, "-", " ") ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,LEN(FirstName)-FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)))+FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,1))&LOWER(MID(FirstName ,FIND(" ",MID(SUBSTITUTE(FirstName, "-", " ") ,FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+1,LEN(FirstName )-FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)))+FIND(" ",SUBSTITUTE(FirstName, "-", " ") ,1)+2,LEN(FirstName)-1)) ) )