This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi All - I am trying to maintain consistency between about 1,500 names in my data set. The names include hyphens, such as Ann-Marie and apostrophes, such as O'Connor. Using just a titlecase formula converts the names to "Ann-marie" and "O'connor" respectively. I tried to use a formula to correct the issue, but was only able to fix 1 of the 2 issues. The formula I used to fix hyphenated names is shown below, however, I cannot seem to figure out how to amend this to also work with the apostrophe issue. I also tried to use Regex replace formulas but I was unsuccessful in deciphering how the formula works.
You can use a conditional statement to check if the value contains an apostrophe or hyphen. If they do, then the replace formula is used. Otherwise, a standard titlecase formula is used. See attached for an example.
if contains([Manager Lvl 2], "-") then Replace(TitleCase(ReplaceChar([Manager Lvl 2], "-", '$')), '$',"-") elseif contains([Manager Lvl 2], "'") then Replace(TitleCase(ReplaceChar([Manager Lvl 2], "'", '$')), '$',"'") else TitleCase([Manager Lvl 2]) endif
I decided to also try the solution provided by @MarqueeCrew as I would like to learn more about using Regex formulas, but found that it did not correct either instance of the issue in my dataset. I was also unable to follow the logic with the proposed solution due to my inexperience so I could not even give an attempt to rework the formula.