+ Start a Discussion
RelaxItsJustCodeRelaxItsJustCode 

Has anyone ever created a formula to proper case fields?

Has anyone ever created a formula to proper case fields?

 

Thank you,

Steve

sfdcfoxsfdcfox

Steve,

 

What are you looking for?

 

  • I am proper case. (String.capitalize)
  • I Am Proper Case. (String.split -> String.capitalize -> String.join)
  • I am Proper Case. (String.split -> Determine if capitializing / String.capitalize -> String.join)

The solution to your inquiry won't be a formula, at least not without a workflow rule, but more likely Apex Code that could process the field using a combination of String.splitbycharactercamelcase, String.capitalize, and String.join (new Winter '13 features).

jessimcqjessimcq

Try this one out. It capitalizes multiple words and has worked excellently so far for us. Just replace LastName with any field you want to use it on!

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

Anthony GoreAnthony Gore
@jessimcq: thanks for that, it must have been quite a task to put that formula together!

I've added two slight improvements to better fulfil the need, and fixed an error
  1. The formula now puts the field in proper case i.e.if the input string is "CITY" it will now return "City"
  2. The formula allows strings of any length now. This is not a particularly useful improvement, since the formula already has the limitation of only proper casing three words, but it just makes more sense than capping a word at 100 characters, as you currently have it.
  3. There's an error as well. You're concatenating a space in the middle of strings with multiple words. This is unnecessary and will actually increase the whitespace in the string if you run the filter multiple times.
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))
  )
)

Russell baker 1Russell baker 1
Hi Can you please tell me where i can write this formula. I tried to put in formula editor  of last name but it is giving me an error.
Amanda Byrne- Carolina Tiger RescueAmanda Byrne- Carolina Tiger Rescue
@[Russell baker 1] Did you remove the line numbers that were copied when you copied the text? The line numbers are added when you use the "<>" button to insert code into an answer for reference purposes, but should not be included as part of the formula
Yuka NoguchiYuka Noguchi
@Anthony Gore, thank you for your insight. I tried it and it works for up to 3 words, not like the 100 cap you mentioned. Can you take a quick look and see why that might be happening? It would be greatly appreciated! 
Travis DvorakTravis Dvorak
Is it possible to have this only work when the value is greater than 2 digits.  I'm using this for the State field and if a lead is submitted using the proper case, (NE for example), it converts it to Ne.  Wondering if there's any kind of login that will only update for 3 characters or more?
Ken S (OLD ACCOUNT)Ken S (OLD ACCOUNT)
@jessimcq @Anthony Gore This is BRILLIANT! Most everyone elsewhere was claiming you needed Apex code for this. I'm not even going to attempt to evaluate this formula in my head right now...LOL.

However, hoping one or both of you could brilliant formula gurus could add some additional tweaks to @Anthony Gore's version:

1) Is it possible to expand it (without going over compile limits) to account for up to 5-6 spaces? For example, won't work with some longer person-based common street names used throughout the US, e.g. "3500 John F. Kennedy Blvd" (turns into 3500 John F. kennedy blvd) :-\

2) Could you add logic to treat dashses as spaces also, e.g. Mary-Ellen? Apostrope too, perhaps. That would be amazing! Although depending on use case (e.g. ethnic name origin, first vs. last name), preference may be to capitalize or not capitalize the following character (e.g. A'niyah vs. O'Dell)
Anthony BrightAnthony Bright
I replied earlier, but in haste, as my plug-and-play did not fully work as i wanted it to. @jessimcq and @Anthony Gore, thank you for doing a lot of the leg work. My edit to the formula simply allows for hyphens. I replaced the snippets of 'FIND(" ", City' and 'FIND(" ",MID(City' with 'FIND(" ",SUBSTITUTE(City,"-", " ")' and 'FIND(" ",MID(SUBSTITUTE(City,"-"," ")'. This essentially converts hyphens to spaces, while at the same time, still looking for any spaces already entered. Though, the final product of the formula will retain the hyphens. My first iteration of the formula removed the hyphens completely. Below is the updated formula that retains hyphens.
IF (
    FIND(" ", SUBSTITUTE(City, "-", " ") ,1)=0, UPPER(LEFT(City ,1))& LOWER(MID(City,2,LEN(City)-1)),
        IF(
            FIND(" ",MID(SUBSTITUTE(City, "-", " ") ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,LEN(City)-FIND(" ",SUBSTITUTE(City, "-", " "),1)))=0,
            UPPER(LEFT(City ,1))&LOWER(MID(City ,2,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)-1))&UPPER(MID(City ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,1))&LOWER(MID(City ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+2,LEN(City)-1)),
            UPPER(LEFT(City ,1))&LOWER(MID(City ,2,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)-1))&UPPER(MID(City ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,1))&LOWER(MID(City ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+2,FIND(" ",MID(SUBSTITUTE(City, "-", " ") ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,LEN(City )-FIND(" ",SUBSTITUTE(City, "-", " ") ,1)))-1))&
            UPPER(MID(City ,FIND(" ",MID(SUBSTITUTE(City, "-", " ") ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,LEN(City)-FIND(" ",SUBSTITUTE(City, "-", " ") ,1)))+FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,1))&LOWER(MID(City ,FIND(" ",MID(SUBSTITUTE(City, "-", " ") ,FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+1,LEN(City )-FIND(" ",SUBSTITUTE(City, "-", " ") ,1)))+FIND(" ",SUBSTITUTE(City, "-", " ") ,1)+2,LEN(City)-1))
)
)
John GuerriereJohn Guerriere
have been following this thread for a while....we just released an App for proper casing names....it's free for nonprofits and has a free trial period...

https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FMnRpUAL