Something that cropped up today. Excel has some handy functions for quickly changing the case of text, in bulk.

One is PROPER – this function capitalises the first letter of each word, and sets the rest to lowercase. To use this function you just type =PROPER(A2) in a new cell, and swap ‘A2’ for the target cell you want to modify.

So far, so good.

However, there’s a limitation using the Proper case function in Excel – it capitalises all characters immediately after an apostrophe. This is because it’s designed to work for names such as O’Leary.

Which is fine for most names, but means that other words are affected. For instance DON’T is transformed to: Don’T

How to eliminate capital letters after apostrophes

To eliminate capital letters after apostrophes you can use the following function:

=SUBSTITUTE(PROPER(SUBSTITUTE(A2,”‘”,”qx”)),”qx”,”‘”)

(change ‘A2’ to the original source cell, then drag down the bottom-right corner of the cell to apply to other cells in that column)

How does it work? Well, it goes through, changes all apostrophes to text (“qx”), converts all text to Proper case, then swaps the qx back to an apostrophe. A hack, but a novel one!

This will eliminate all the capitals after apostrophes, with a notable exception…

Apostrophes that follow integers, eg: 70’S – which ends up as 70Qxs. This is possibly due to Excel converting apostrophes following integers to smart quotes. A theory I haven’t tested TBH.

Correct use of apostrophes in decades

But strictly speaking, the decades shouldn’t have apostrophes there anyway – they are plural. The apostrophe is in this instance a contraction of 1970s, therefore should read: ’70s.

Anyway, here are three ways around this:

  1. Simply find and replace “Qx” with nothing (ie delete it). This removes the apostrophes following integers altogether.
  2. Find and replace “Qxs” with “’s” (which reinstates the original apostrophe).
  3. Use this additional function to remove the qx and reinstate the apostrophe:
    =SUBSTITUTE(SUBSTITUTE(D2,”qx”,”‘”),”Qx”,”‘”)

So, a little bit of work (well a bit of explaining anyway) for what should probably be an option or additional function in Excel (where you choose how it handles apostrophes within words).