+ Start a Discussion
Colin LammersColin Lammers 

Proper Case Fields

So I found a formula in this link that solves 75% of my formatting problems in salesforce (https://developer.salesforce.com/forums/ForumsMain?id=906F0000000927nIAA)

The problem is it's limited to the first three set of text queries in a field (e.g. John Deere Green but a fourth word like tractors wouldn't be impacted). I'm trying to figure out how to expand it past three words. Any help? The biggest impact this would have is in the address field (4242 John Wayne street). It'd be great to capitalize the S of street in that field.
Best Answer chosen by Colin Lammers
Alain CabonAlain Cabon
Hi,

The formulas are not suitable at all for splitting/formatting text. I have played the game of writting this huge formulas but it is not the best solution. 

As you noticed the current formula you found is already almost not updatable without help (for each new word, the formula becomes more complicated and there is a limit for the compiled formula quickly reached).

It is much more easy to write a trigger for these tasks of splitting/formatting text and the short apex code below is easy to understand and maintain.

The street can be multiline and the code below has almost no limit for the number of words (and lines) without any change needed for each new word. 

word.trim().tolowercase().capitalize();  transforms "  sTrEet  " into "Street"
trigger AccountBeforeInsertUpdate on Account (before insert,before update) {
    for (Account acc:trigger.new) {
        //  AccountHandlerUpdate.updateData(acc.id);
        String accstreet = acc.BillingStreet;
        if (!String.isEmpty(accstreet)) {
            String [] lines = accStreet.split('\n');
            integer j = 0;
            for (String line:lines) {
                String [] words = line.split(' ');
                integer i = 0;
                for (String word:words) {
                    words[i] = word.trim().tolowercase().capitalize();
                    i++;
                }
                lines[j] = String.join(words,' ');
                j++;
            }   
            acc.BillingStreet = String.join(lines,'\n');
            system.debug(acc.BillingStreet);
        }       
    }
}

And if there is no other answer until now, it is perhaps because no one wants to play the game of the "ridiculous" formula anymore when you can write a short code of apex (but someone will perhaps write the formula you are expected over the next few days for the fun).

Regards

All Answers

Alain CabonAlain Cabon
Hi,

The formulas are not suitable at all for splitting/formatting text. I have played the game of writting this huge formulas but it is not the best solution. 

As you noticed the current formula you found is already almost not updatable without help (for each new word, the formula becomes more complicated and there is a limit for the compiled formula quickly reached).

It is much more easy to write a trigger for these tasks of splitting/formatting text and the short apex code below is easy to understand and maintain.

The street can be multiline and the code below has almost no limit for the number of words (and lines) without any change needed for each new word. 

word.trim().tolowercase().capitalize();  transforms "  sTrEet  " into "Street"
trigger AccountBeforeInsertUpdate on Account (before insert,before update) {
    for (Account acc:trigger.new) {
        //  AccountHandlerUpdate.updateData(acc.id);
        String accstreet = acc.BillingStreet;
        if (!String.isEmpty(accstreet)) {
            String [] lines = accStreet.split('\n');
            integer j = 0;
            for (String line:lines) {
                String [] words = line.split(' ');
                integer i = 0;
                for (String word:words) {
                    words[i] = word.trim().tolowercase().capitalize();
                    i++;
                }
                lines[j] = String.join(words,' ');
                j++;
            }   
            acc.BillingStreet = String.join(lines,'\n');
            system.debug(acc.BillingStreet);
        }       
    }
}

And if there is no other answer until now, it is perhaps because no one wants to play the game of the "ridiculous" formula anymore when you can write a short code of apex (but someone will perhaps write the formula you are expected over the next few days for the fun).

Regards
This was selected as the best answer
Colin LammersColin Lammers
Thanks for the advice. Unfortunately, we don't have have access to triggers and apex code with our edition of Salesforce so we'll either have to upgrade or settle with the limitations of that code.
Alain CabonAlain Cabon
Ok, you should have indicated this constraint (my solution is useless for you).

You should also tried other forums (formulas are often solved by their experts everyday and many people like these challenges without limit)

https://success.salesforce.com/answers?feedtype=RECENT (https://success.salesforce.com/answers?feedtype=RECENT&criteria=BESTANSWERS)

Regards
John GuerriereJohn Guerriere
Alain Cabon - thank you for sharing this apex code. Very helpful. Nice work.