4

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

2
  • 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)) Which resolves all the issues expect for the McCloud scenario
    – Masond3
    Jan 2, 2015 at 16:41
  • Welcome to SF.SE Masond3. I've edited your post to update it with your revised formula. You can always edit your posts by clicking on "edit" at the bottom left below them. Please take the time to visit Help center and take our Tour to better understand how this forum works. We want your experience here to be a positive one where you'll be able to receive the answers you need and contribute to the community if you choose to. Thank you.
    – crmprogdev
    Jan 2, 2015 at 17:06

3 Answers 3

2

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?

1

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();
    }
2
  • Hi Crop1645 I am not very good at reading code, is it possible if you can explain this code to me ?
    – Masond3
    Jan 8, 2015 at 16:50
  • Masond3 -- The best way to understand it is via the testmethod. For example, if the incoming all caps text JOE O'LEARY is received, it converts it to Joe O'Leary. If you are not familiar with Apex code and want a solution that does not rely on code, then this answer isn't for you. An Apex resource to start with would be developer.salesforce.com/docs/atlas.en-us.apex_workbook.meta/…
    – cropredy
    Jan 8, 2015 at 17:26
0

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)) ) )

2
  • Welcome to Salesforce Stack Exchange (SFSE)! I cannot tell if this is an answer with additional info/commentary, or if it is a question. Would you please clarify?
    – Moonpie
    Dec 13, 2022 at 22:32
  • this problem is intractable with formulas if you want to handle the special cases as shown in @cropredy answer; even the Apex solution as shown is undoubtedly missing some special cases
    – cropredy
    Dec 13, 2022 at 22:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .