Thursday, 27 May 2010

3Rd Floor Flat

Should TitleCase("3rd floor flat") resolve to 3Rd Floor Flat? Alteryx thinks it does, I'm not so sure...  But the question I had from a user on Friday was how do I turn "3rd floor flat" into "3rd Floor Flat"?  Which of course is what you would want on an address envelope. 

My first solution involved using a regex tool to pull the first letters out then the dynamic formula macro to build the string back together captilasing the first letters.  I wasn't overly happy with the solution though  (far too many tools for a simple task) so after some more thinking have come up with the following formula:

REGEX_REPLACE(TitleCase(REGEX_REPLACE([Field1], "(\d)", "$1X")), "(\d)X", "$1")

(which I think should always work...)

Anyone think they can come up with a more elegant solution?