Wednesday, 6 May 2009

OpenOffice.org Proper / Title Case

Today I had to convert thousands of lines of text in OpenOffice Calc to title/proper case. I could have scripted it, but it felt like OpenOffice *should* have this sort of functionality built it. Under the Format->Change Case menu options, there are Uppercase/Lowercase options, but no title/proper case.

I found a couple of old macros that purportedly did the job - they didn't work and I really didn't fancy fucking about with VBScript or whatever the hell it is. In my case, the simplest way to do this was to create a neighbouring column and enter =PROPER(A1) with A1 being the neighbouring cell. Copy this simple formula down the rest of the column, copy the values and paste-special the strings. Simples!

I'm sure there are more elegant ways to do this but I had a deadline and I didn't really fancy any extra legwork, just to get the values converted. Hopefully this will save someone some time dicking about with macros that don't work and other such irritants.

5 comments:

Marcus said...

You just saved me a lot of work. Thank you.

cm said...

Glad to hear it - that was my intention with this post as it's not particularly intuitive in Calc. What I don't get is why OpenOffice already has the logic to do this yet has neglected to add an additional menu option for proper case.

Andrew said...

Thanks very much for posting this. I was about to give up on two thousand rows that needed exactly this fix. Hopefully they add in a feature that does this soon.

ImagicDigital said...

Thank you, thank you, thank you. Viva la PROPER.

cje said...

I'm confused, so you have to manually enter the corresponding cell number each time as you copy the formula down the column? If so, how is this any better than manually changing to upper and lower. There must be a way to automate this process.