Just imagine that you have a list (a long list) of names in a spreadsheet (no, it's not suitable for export and processing as a CSV) that are unhelpfully in the wrong format. In my case they looked like "FredBloggs" instead of "Fred, Bloggs".

If you happen to be using a decent, open spreadsheet this is easy to fix as there's a "Regular expressions" checkbox on the Find and Replace dialog.

However if you're stuck with a proprietary spreadsheet life is a little harder. Excel has VB hiding in there somewhere though, so it had to be possible. Thankfully the Internet knows all and someone else has already written the code. Just to make sure I don't loose it, I'm going to reproduce the bit I found useful here:

Option Explicit

Function ReReplace(ReplaceIn, _
    ReplaceWhat As String, ReplaceWith As String, Optional IgnoreCase As Boolean = False)

    Dim RE As Object
    Set RE = CreateObject("vbscript.regexp")
    RE.IgnoreCase = IgnoreCase
    RE.Pattern = ReplaceWhat
    RE.Global = True
    ReReplace = RE.Replace(ReplaceIn, ReplaceWith)
End Function

Once that had been added to my spreadsheet as a module, I could use something like this to do my conversion:

=ReReplace(A3, "^([A-Z][^A-Z]+)([A-Z])", "$1, $2")

Yes, groups and back references are all supported (as they are in the Open Office find and replace dialog)!

Having copied the new cell down a whole column I had my results. All that remained was a copy/paste of the values to overwrite the originals with the corrected formatting.