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
Andy Coustick-DealAndy Coustick-Deal 

Find + Substitute is Deleting Extra Letters

Good people of the developer community,

I am trying to convert a custom text Name field into a truncated version which replaces with spaces with hyphens and gets rid of periods and parentheses.  I'm using the Find + Substitute combo to get this done, but I'm finding something strange.  It is doing everything I am telling it to, except it is sometimes deleting the first letter of each word as well!

For example: "Mr. (John) Smith" should become "Mr-John-Smith".  However it is becoming "r-ohn-Smith"

The code is below:

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(

Display_Name__c, MID(Display_Name__c, FIND(")", Display_Name__c, 1), 1), ""),

MID(Display_Name__c, FIND("(", Display_Name__c, 1), 1), ""),

MID(Display_Name__c, FIND(".", Display_Name__c, 1), 1), ""),

MID(Display_Name__c, FIND(" ", Display_Name__c, 1), 1), "-")


Can anyone advise what I'm doing wrong here?  I could understand if it was not doing one of the substitutions correctly and replacing the first letter instead, but it is doing all of the substitutions correctly, so I don't see why its making extra changes.

Thanks in advance,
Andy
Best Answer chosen by Andy Coustick-Deal
Jim JamJim Jam
Maybe try this ..

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Display_Name__c," ", "-"),".","" ),"(",""),")", "")

This way you don't have to to any FINDS and substrings using MID. Your problem is probably a data issue when there are spurious space characters in the string I guess, which mess up your numbers.

All Answers

Jim JamJim Jam
Maybe try this ..

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Display_Name__c," ", "-"),".","" ),"(",""),")", "")

This way you don't have to to any FINDS and substrings using MID. Your problem is probably a data issue when there are spurious space characters in the string I guess, which mess up your numbers.
This was selected as the best answer
Andy Coustick-DealAndy Coustick-Deal
That was outstanding, thank you!  Fixed the problem.  I thought there must be extra spaces or something, but checking the individual records I couldn't find any.  Anyway, it worked and it made the formula simpler on top of that!  Thanks a lot for your help.

Best regards,
Andy