Hi all;
Wonder if anyone can offer a little help? I'm not much good at VBA, probably because I use it so rarely, but I've managed to almost get something working just right -but not quite!- We process a lot of peoples' names in our business, and clients submit thousands of them to us in all sorts of ways. Ideally they send us an Excel spreadsheet with columns for first, middle and last names.

But sometimes we get a spreadsheet with a single column in the format:
FIRSTNAME Lastname, for instance:
PETERSON John
JOHNSON Peter William
O'REILLY Emma
HANDFORD-WILLIAMS Jenny
McMURDO Phillip Frederick

I found and modified the following bit of code to process this into the format we need them in, i.e. all names in a single column in order of first name, middle names, then last name. It works just fine, except for a few things.
1. Does anyone know how to get an apostrophe chr(39) into the Like charlist on line 3? It fails if I add it in after the hyphen, and I need it to process names like O'REILLY.
2. It also doesn't like first names like MacSWEEN or McMURDO, as the lowercase letters throw a spanner in the works and end the macro too early. Is there a way of getting the Like operator to include these too?

Code below, many thanks folks!

Function getname(rng As Range) As String
Dim getcaps As String
For x = 1 To Len(rng)
    If Mid(rng, x, 1) Like "[ABCDEFGHIJKLMNOPQRSTUVWXYZ-]" And Not Mid(rng, x + 1, 1) Like "[a-z]" Then
        If Mid(rng, x + 1, 1) <> " " Then
        getcaps = getcaps & Mid(rng, x, 1)
            Else
            getcaps = getcaps & Mid(rng, x, 1) & " "
        End If
    End If
Next

getname = Trim(Mid(rng, Len(getcaps), Len(rng))) & " " & Trim(Mid(getcaps, 1, Len(getcaps)))

End Function