Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 18, 2017 05:27 PM
Hello all! I’ve searched and have not been able to find a solution for this.
I want to be able to reference a column and change all names that appear in one column to PROPER names (which can easily be done in excel)
Use Case: Folks are signing up for a service we offer and they input their names in some crazy ways. But we want to convert sloppy username case with Proper case so that the certificates we offer look consistent and classy, instead of having things like:
john doe
JOHN DOE
John DOE
etc.
We’d like to take those examples and be able to uses a “PROPER” formula to change them to autocorrect to “John Doe”
Thanks for any help with this.
(hoping there’s some formula or hack we can use that doesn’t require some extra manual steps!)
Dec 19, 2017 03:08 AM
I think we have not that feature, check this: New Text Function: PROPER ( )
Maybe you could do it with Zapier?
Dec 19, 2017 12:40 PM
The answer, as always, is “it depends.”
If this was a {firstName}{lastName}
universe, it would be trivial to create a formula to fix proper names. But even if you try to force the issue by prompting for such input, some joker like me is going to come along and shove ‘W Vann’ into the {firstName}
field, and all bets are off. That said, it should be possible to come up with a formula that almost always gets things right.
Well… almost always gets things rightish. Case in point: Google ‘di castro’. Skimming down the first page of responses, I find Anna Di Castro, Jane di Castro, Angelica DiCastro (who later gives her name as Angelica Di Castro), Joseph DiCastro, and Simon Dicastro. Short of a Zap that integrates with God, you are going to get somebody’s name wrong…
If your data is arriving as {firstName}{lastName}
, life is much simpler — so I will assume you are starting with {Name}
, because that’s life.
My [other] assumptions: Up to five name components per name, separated by spaces or a hyphen, including prefixes and suffixes; no more than one hyphenated name segment; a minimum of two name components (sorry, Sting and Cher!).
And the results?
The good news: It can be done, with no need for middleware or manual intervention.
The bad news: It’s a bit of a mess.
Here is a base demonstrating my take on a proper name function. (Actually, this is a third iteration of my earlier name processing demo base with a newly added table containing the proper name routines. The two sets of processes are not integrated.)
As implemented, this algorithm requires 10 (!) — 9 typically hidden — fields to perform a reasonably rightish proper name formatting. Conceivably, it could be implemented more compactly with the many FIND()
functions incorporated inline rather than by reference; I chose to break the process into chunks in hopes of making it somewhat intelligible.
My handling of hyphenated last names is, well, a little disappointing. Rather than having to search for a space OR a hyphen three times or perform two separate passes, I cheat: If a hyphen exists, I note its location in the string, replace it with a space, capitalize the first character and any character that follows a space, and return the hyphen to the string.
Since much of the processing is triggered by the presence of space characters, leading and trailing spaces are trimmed, and redundant internal spacing normalized. Finally, in support of dynasties, the suffixes ‘II’, ‘III’, and ‘IV’ are explicitly corrected. (If your students are particularly proud of their credentials, you may need to add support for ‘MD’, ‘PhD’, and the like.)
Needless to say, the routines are very English-language, Western (U.S.) society biased, and your mileage may vary.
Still, you should be able to use these routines confident the resulting formatted names will usually be more-or-less rightish — which is about the best one can ask when it comes to names. Inevitably, just when you think you have provided for any eventuality, your next sign-ups will be from bell hooks, e. e. cummings, The Artist Formerly Known As Prince, and Kiefer William Frederick Dempsey George Rufus Sutherland.
Sep 10, 2019 10:40 AM
For whomever comes across this brilliant solution, I have found a small bug where it doesn’t “re-hyphenate” correctly names where the hyphen is between the first and second word, eg. Josep-Maria RAMUNES).
To fix, implement the following changes:
& MID( noHyph, 2, Sp1-1 )
to & MID( noHyph, 2, Sp1-2 )
. (Sp1-1 becomes Sp1-2)hyphenIndex+1
to hyphenIndex
And for what it’s worth, this solution works well with accented letters too! àéü etc.
Nov 27, 2019 01:25 PM
From this:
animals > cat > face > eyes
into
Animals/Cat/Face/Eyes
how can i do that i know the first step:
SUBSTITUTE({Category 1}, ’ > ', ‘/’) // this replaces the > into /
and then how can i use the upper case method only for the first letter of every word?
thanks for the help
please write an example code, thanks!
Jul 25, 2020 10:33 AM
Hi there! Thanks for this brilliant solution. However, if the user enters say “Martin McDonald Jr” then the formulas don’t seem to work - they output the last name as “Jr” or “JR" - is there an easy way to accomodate this if a user enters the name that way?
Nov 30, 2020 05:35 PM
I love this solution and it is working well in my instance. I am having the same issue that @Brandon_Smith1 references. When the name ends in “Jr” without the “.”, it renders Jr as the last name. Is there a bug in the formula?
Jul 29, 2021 02:08 PM
@Jordan_Dayton you can also use the formula mentioned here: We still need a simple TITLE CASE (PROPER) function alongside UPPER and LOWER - #6 by gxjansen
Jan 11, 2022 04:34 AM
Assuming that you’ve already split and trimmed the full raw name into first and last names, here’s a formula that works for me:
UPPER(LEFT(FirstName,1))&MID(FirstName, 2, LEN(FirstName)-1)
It grabs the first letter of the name, capitalizes it then adds the these of the name minus the first letter.