function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
AardvarkAardvark 

Best Way to parse Account.Name?

So I have object Account.name which I need to take the last word of the string and place it at the beginning. For example, I have John and Jamie Jones and I need the custom field to read: Jones, John and Jamie
Best Answer chosen by Aardvark
Alain CabonAlain Cabon
@Aardvark

That could be surprising that a so simple question has no answer but it is very complicated with a formula because the suitable functions for this work are just missing.

We can find the first words "easily" but the formula becomes more an more complex for each new word to find at the beginning.

The last word is the most difficult to find with a formula.

FIND always starts from the beginning (left to right) so the function RIGHT is almost useless.
REGEX (for a formula field) and SPLIT are just missing.

The workaround is a trigger in Apex that will update a new field of type String with the last word at the beginning each time you create/update the object. In Apex, the last word is easy to find. 
  
String st1 = 'John and Jamie Jones';
integer last_space_or_comma = Math.max(st1.lastIndexOf(' '),st1.lastIndexOf(','));
System.debug('st:' + st1);
String st2 = (last_space_or_comma != -1 ? (st1.substring(last_space_or_comma +1).trim() + ', ' + st1.substring(0,last_space_or_comma).trim()):st1);
System.debug('st2:' + st2);

All Answers

Alain CabonAlain Cabon
@Aardvark

That could be surprising that a so simple question has no answer but it is very complicated with a formula because the suitable functions for this work are just missing.

We can find the first words "easily" but the formula becomes more an more complex for each new word to find at the beginning.

The last word is the most difficult to find with a formula.

FIND always starts from the beginning (left to right) so the function RIGHT is almost useless.
REGEX (for a formula field) and SPLIT are just missing.

The workaround is a trigger in Apex that will update a new field of type String with the last word at the beginning each time you create/update the object. In Apex, the last word is easy to find. 
  
String st1 = 'John and Jamie Jones';
integer last_space_or_comma = Math.max(st1.lastIndexOf(' '),st1.lastIndexOf(','));
System.debug('st:' + st1);
String st2 = (last_space_or_comma != -1 ? (st1.substring(last_space_or_comma +1).trim() + ', ' + st1.substring(0,last_space_or_comma).trim()):st1);
System.debug('st2:' + st2);
This was selected as the best answer
Alain CabonAlain Cabon
st1 = st1.trim();    // We never know 
  
  String st1 = 'John and Jamie Jones';
  st1 = st1.trim();
  integer last_space_or_comma = Math.max(st1.lastIndexOf(' '),st1.lastIndexOf(','));
  system.debug('st:' + st1);
  String st2 = (last_space_or_comma != -1 ? (st1.substring(last_space_or_comma +1).trim() + ', ' + st1.substring(0,last_space_or_comma).trim()):st1);
  system.debug('st2:' + st2);

 
AardvarkAardvark
Thanks Alain, I figured with an Apex trigger things would have been much simpler. I was just hoping there was some formula magic I was unaware of. I'll build a trigger based on your advice. Thanks! 
Alain CabonAlain Cabon
@Aardvark 

A trigger is a workaround but ideally, that would be better to control the content of the field upstream as far as possible.

You will also have the common problem of the recursive calls because you update an object based on a field of itself.
https://help.salesforce.com/articleView?amp;language=en_US&id=000332407&type=1 (https://help.salesforce.com/articleView?amp;language=en_US&id=000332407&type=1)

A new field is not mandatory either.

We can try to build a giant formula (as long as we don't explode the compilation limit), that is likely possible if you know about many words at the maximum. Even if it is just first and last names, that could be very long.

There will be a formula for only the last word plus a formula for the first part before the last word and a third formula to concatenate the two parts.

At the end, we will have a very restricted formula that works with five or six words max. precisely (or perhaps less) but that will be restricted because of the limit size of a compiled formula.

So the four lines of code for a trigger is effortlessly.

Formula for the last word : works up to seven words. 
CASE( LEN(FirstLastNames) - LEN(SUBSTITUTE(FirstLastNames,' ', '')),
1, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames)),
2, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames, FIND(" ", FirstLastNames)+1)),
3, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames)+1)+1) ),
4, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames)+1)+1)+1) ),
5, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames)+1)+1)+1)+1) ),
6, RIGHT(FirstLastNames, LEN(FirstLastNames) - FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames, FIND(" ", FirstLastNames)+1)+1)+1)+1)+1) ),
FirstLastNames
)

But that is not enough in your case because you want to reformat the field so you need two other formulas and that will be a really very huge formula at end but still interesting to try perhaps.
 
Alain CabonAlain Cabon
if you know about how many words at the maximum. Even if it is just first and last names, that could be very long.
Alain CabonAlain Cabon
The formula above works with a perfect series of words separated with exactly one space and nothing else so there is again an extra formula to create with a replacement of two spaces by one.
A replacement of two spaces by one ( SUBSTITUTE(FirstLastNames,'  ', ' ')  has to be repeated with nested calls because you can have any number of spaces between the words. SUBSTITUTE ( SUBSTITUTE(  SUBSTITUTE(FirstLastNames,'  ', ' '), '  ',' '), '  ', ' ') )
Up to eight spaces otherwise you need a new nested substitute.

At the end, the formula becomes too huge for the limited compiled size.
 
S.Matt ReadS.Matt Read
@Alain This formula worked great for me. Having a "Jr." at the end did trip it up, so I'm still trying to figure that one out. But thank you!