Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ 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
Julie Baxter 18Julie Baxter 18
@john guerriere - your app is fantastic! 
Scott Smith 107Scott Smith 107

If anyone is still looking, this formula will work in Process Builder. We are currently using it to format names:

UPPER(LEFT([Object].FirstName,1))&LOWER(Mid([Object].FirstName, 2, Len([Object].FirstName)-1))

This takes:

SCOTT SMITH and turns it into Scott Smith

scott smith and turns it into Scott Smith

I hope this helps someone!
Raghuram GowlikarRaghuram Gowlikar

@Scott Smith 107 

I am working on process builder, but given formula is not working to me. Please update me if changes or suggest me how should i 

Thanks
Heather Hanson 32Heather Hanson 32
@Anthony Bright

Thank you for your formula!  I'm using it for our Name fields on Person Accounts since we will begin using these fields for correspondence. Your formula works great, except we have a lot of "Mac"s where I'm from (MacDonald, MacNeil) and so it doesn't seem to work if there is no space or hyphen (which makes sense).  Do you have any suggestion how this can be improved to ensure these names have proper casing so that if someone inputs MACDONALD, it doesn't switch to Macdonald, but MacDonald (or Mac Donald would be acceptable too)?
 
Andrew StevensonAndrew Stevenson
Just wondering if anyone has got a formula working for "Mac"s and "Mc"s?
Miriam BetznerMiriam Betzner
@AnthonyBright's formula worked brilliantly with our FirstName and LastName fields, also where there are hypens. Looking for an addition to inlude ' in names, like O'Brien, which currently gets changed to O'brien with the string. I have ZERO expereince in writing these formulas. We recently migrated our older workflows to Flow builder, but from what I can tell they use the fomular we had for the Workflows still.
Anthony BrightAnthony Bright

Hi @Miriam Betzner,

Try the below iteration and let me know if that works.

Thanks,

Anthony

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