How can you change data from all caps to proper case? - Answers - Salesforce Trailblazer Community
Trailblazer Community

Browse by categories

Ask Search:
ShowAll Questionssorted byDate Posted
Janis DavisJanis Davis 

How can you change data from all caps to proper case?

I have a ton of Lead and Contact data that have the Company and Names in all uppercase or lower case which makes it difficult to utilize field merges in email blasts. Does anyone know a way to append data from all upper or all lower case to proper case? If the is not a way to do it with a validation or workflow rule are the any tools out there on the AppExchange that can be used to accomplish this?
Best Answer chosen by Moderator (salesforce.com) 
Matthew LambMatthew Lamb
No way to do this in Salesforce that I'm aware of natively. You could write a batch Apex method (similar to this: http://boards.developerforce.com/t5/Apex-Code-Development/Convert-To-Title-Case/td-p/68592) that would go through your data and correct it, but that's development work, which may or not be feasible depending on your skill set.

You can do this with Excel by using the PROPER function (http://www.cornerstonesolutions.com/help/Excel_Changing_Data_to_Proper_Case.pdf), so one option is to export the data (if you're using Professional you'll need the Excel Connector, or if you're on Enterprise or Unlimited you can use the Data Loader) to Excel, adjust it there, and update it back into Salesforce.

All Answers

Matthew LambMatthew Lamb
No way to do this in Salesforce that I'm aware of natively. You could write a batch Apex method (similar to this: http://boards.developerforce.com/t5/Apex-Code-Development/Convert-To-Title-Case/td-p/68592) that would go through your data and correct it, but that's development work, which may or not be feasible depending on your skill set.

You can do this with Excel by using the PROPER function (http://www.cornerstonesolutions.com/help/Excel_Changing_Data_to_Proper_Case.pdf), so one option is to export the data (if you're using Professional you'll need the Excel Connector, or if you're on Enterprise or Unlimited you can use the Data Loader) to Excel, adjust it there, and update it back into Salesforce.
This was selected as the best answer
Janis DavisJanis Davis
Thanks!!!!
Lionel Momeni NgaleuLionel Momeni Ngaleu
I just want to share the answer that I"ve seen from another post. It worked fine for me. As long as the name isn't hyphenated.




UPPER(LEFT(FirstName,1))&LOWER(Mid(FirstName, 2, Len(FirstName)-1))
Jeremy PylesJeremy Pyles
You can do this quite easily in the EMAIL TEMPLATE itself with CSS.

For example:
 
Hello <span style="text-transform: capitalize">$firstname</span>

(sorry don't know the actual SFDC variable for $firstname, so you will need to look that up yourself)

This isn't foolproof because most email clients support this CSS, but some don't.
We found it to be easier to do this than to export to Excel, do a proper case function and then reimport. 
The chances of any given lead being ALL CAPS and on an email client that doesn't support this CSS is pretty slim, so most likely it will cover 90% of use cases.

Hope this helps!
 

Jeremy
Alexandria RowlandAlexandria Rowland
@Matthew Lamb -- I didn't realize that the data loader can override the ALL CAPS. I tried it with the importing tools within SF and had no luck. Definitely going to try this. Thanks for the help!
Eve OlynykEve Olynyk
@Janis Davis

You can do this using workflow rules.
Formula for the workflow rule:

AND( 
NOT(ISBLANK( LastName )), 
OR( 
ISNEW(), 
ISCHANGED(LastName) 
))

Formula for the field update action:
UPPER(LEFT(LastName, 1)) & LOWER(RIGHT(LastName, LEN(LastName) - 1))


(and just modify for firstname or account or whatever field you are doing)


*note, I modified a solution found here: https://success.salesforce.com/answers?id=90630000000hNUHAA2 , if you follow the link you can see step by step instructions, so you just need to update the formula to match what I wrote above!

 
Steven PayneSteven Payne
There are a number of ways to do this. Here is my solution for the Lead object:
  • Create a Quick Action Button that will Update a Record.
  • Create a page view without any fields.
  • Set your predefined values to the following:
Predefined values screenshot

I also made an 'instruction' field, which is a text formula field with the formula being a set of instructions on what the button does within quotes. I added this field as the only field showing on the page view of the button.

This is a very old question, but perhaps we could un-thumbs-up the incorrect best answer on this one???
Ben HerouxBen Heroux
Hi Steven.  Thanks for this info.  Question.  If the Company has two words in it's name, will only the first word get fixed with proper case?    EXAMPLE:  Company name comes in as SMITH INVESTMENTS.  Will it get corrected to say Smith investments?
Steven PayneSteven Payne
Hi Ben,

You are correct, the example I posted only capitalizes the first letter of the string. To solve this probelm in my own org, I ended up building a Flow. In essense, the flow will look at the number of spaces in the Company name, and then a set of formula fields I have created will capitalize the first letter after each space.

User-added image
The Subflow does the same thing for the Title field and also updates the other fields I had in my Quick Action.

I would be happy to share this with you. Probably would need to provide you with an unmanaged package. Mine is built on the Lead object and is ckicked off by a Auick Action button.
Tom BarberTom Barber
Steven, this is fantastic! I would be interested in an unmanaged package for this as I am going through this right now. Let me know how to get it. Assumption: I assume this could be modified to do this on record create/edit and not just as a screen flow. I have an org where records are coming in from multiple places and we cannot guarantee that they will ever be in proper case coming in.
Amanda ByrneAmanda Byrne
I create a formula field to create the Proper Case value and standardize things like Street to St, and then a workflow or Process to update a field with that value.  The formulas may not work for EVERY situation, but 99%ish of them.

The address one includes a fix for my state (North Carolina), as that was the vast majority of the addresses in my instance at the time - so may need to alter for that.

For a Name:
  
/* formula created by Amanda Byrne, use freely, please include this comment as credit */ 
/* Subs to capitalize letters */ 
/* Capitalize first letter */ 
UPPER(LEFT(CnP_PaaS_EVT__First_name__c, 1)) & 
RIGHT( 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

IF(CONTAINS(LOWER(CnP_PaaS_EVT__First_name__c), "o'"), 
/* o' is true */ 
IF(CONTAINS(LOWER(CnP_PaaS_EVT__First_name__c), "o'"), 
SUBSTITUTE(CnP_PaaS_EVT__First_name__c, 
MID(CnP_PaaS_EVT__First_name__c, FIND("o'",CnP_PaaS_EVT__First_name__c) , 3) , 
"o'" & UPPER(MID(CnP_PaaS_EVT__First_name__c, FIND("o'",CnP_PaaS_EVT__First_name__c)+2, 1))), 
CnP_PaaS_EVT__First_name__c) 
, 
/* o' is false */ 
IF(CONTAINS(LOWER(CnP_PaaS_EVT__First_name__c), "mc"), 
SUBSTITUTE(CnP_PaaS_EVT__First_name__c, 
MID(CnP_PaaS_EVT__First_name__c, FIND("mc",CnP_PaaS_EVT__First_name__c) , 3) , 
"mc" & UPPER(MID(CnP_PaaS_EVT__First_name__c, FIND("mc",CnP_PaaS_EVT__First_name__c)+2, 1))), 
CnP_PaaS_EVT__First_name__c)), 

/* provides values capitalizing each letter */ 
" a"," A")," b"," B")," c"," C")," d"," D")," e"," E")," f"," F")," g"," G")," h"," H")," i"," I")," j"," J")," k"," K")," l"," L")," m"," M")," n"," N")," o", " O")," p"," P")," q"," Q")," r"," R")," s"," S")," t"," T")," u"," U")," v"," V")," w"," W")," x"," X")," y"," Y")," z"," Z") 

/* Finish formula RIGHT cmd */ 
, LEN(CnP_PaaS_EVT__First_name__c) - 1)

for an address:
  
/* formula created by Amanda Byrne, use freely, please include this comment as credit */ 
/* Subs to standardize Addresses */ 
SUBSTITUTE( 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( 

/* Subs to capitalize letters */ 
/* Capitalize first letter */ 
UPPER(LEFT(DTCI_Mailing_Street__c , 1)) & 
RIGHT( 
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(

IF(CONTAINS(LOWER(DTCI_Mailing_Street__c ), "o'"), 
/* o' is true */ 
IF(CONTAINS(LOWER(DTCI_Mailing_Street__c ), "o'"), 
SUBSTITUTE(DTCI_Mailing_Street__c , 
MID(DTCI_Mailing_Street__c , FIND("o'",DTCI_Mailing_Street__c ) , 3) , 
"o'" & UPPER(MID(DTCI_Mailing_Street__c , FIND("o'",DTCI_Mailing_Street__c )+2, 1))), 
DTCI_Mailing_Street__c ) 
, 

/* o' is false */ 
IF(CONTAINS(LOWER(DTCI_Mailing_Street__c ), "mc"), 
SUBSTITUTE(DTCI_Mailing_Street__c , 
MID(DTCI_Mailing_Street__c , FIND("mc",DTCI_Mailing_Street__c ) , 3) , 
"mc" & UPPER(MID(DTCI_Mailing_Street__c , FIND("mc",DTCI_Mailing_Street__c )+2, 1))), 
DTCI_Mailing_Street__c )), 

/* provides values capitalizing each letter */ 
" a"," A")," b"," B")," c"," C")," d"," D")," e"," E")," f"," F")," g"," G")," h"," H")," i"," I")," j"," J")," k"," K")," l"," L")," m"," M")," n"," N")," o", " O")," p"," P")," q"," Q")," r"," R")," s"," S")," t"," T")," u"," U")," v"," V")," w"," W")," x"," X")," y"," Y")," z"," Z") 


/* Finish formula RIGHT cmd */ 
, LEN(DTCI_Mailing_Street__c ) - 1), 

/* Street Sub Values, starting with removing periods */ 
".","")," Nc "," NC"),"Street","St"),"Avenue","Ave"),"Boulevard","Blvd"),"Circle","Cir"),"Court","Ct"),"Crt","Ct"),"Cove","Cv"),"Drive","Dr"),"Heights","HTS"),"Hwy","HWY"),"Lk","Lake"),"Lane","Ln"),"Mount","Mt"),"Mountain","Mtn"),"Po Box","PO Box"),"Pkwy","PKWY"),"Place","Pl"),"Point","Pt"),"Rte","RTE"),"Road","Rd"),"Street","St"),"Terrace","Ter"),"Trace","Trce"),"Trail","Trl")," Ne "," NE ")," Se "," SE ")," Nw "," NW ")," Sw "," SW ")

 
Amanda ByrneAmanda Byrne
(obviously you will need to copy and replace the field names)
Steven PayneSteven Payne

@Tom Barber -- sorry for the delay in getting round to this. Here is an unmanaged package of the flow: https://login.salesforce.com/packaging/installPackage.apexp?p0=04t1Y000000cZ2h

Let me know how it goes.