Macro to just leave first names in column

[tonywatsonhelp]

Hi Everyone,
Column C holds a list of names thats not very clean,
I was hoping there was a way to remove everything but the first name from each cell?
so I need a macro then when run is quite cleaver and leaves me with just first names,
I've tried doing it with a list of things to remove but quite honestly its such a mess its hard as the list get longer and longer so how about this?
a macro to run in column C that remove everything but the first name by looking at the cell finding the first string of letters longer than 1 that has a space after it or is the only word and removes everything else?

I'm thinking I cant be the first person to have this problem so hopfully someone know a great way to do it?
thanks
Tony

 

[GWteB]

How about ...

Public Sub tonywatsonhelp()

    Dim Sht As Worksheet
    Set Sht = ThisWorkbook.Worksheets("Sheet1")     ' <<< change sheet name to suit
    With Sht
        Dim Rng As Range
        Set Rng = .Range("C2:C" & .Cells(.Rows.Count, "C").End(xlUp).Row)
        Dim arr As Variant
        arr = Rng.Value
    End With
    Dim i As Long
    For i = 1 To UBound(arr)
        arr(i, 1) = Split(arr(i, 1), " ")(0)
    Next i
    Rng.Value = arr
End Sub

 

[Rick Rothstein]

Here is another macro that you can try...

Sub GetFirstNames()
  With Range("C2", Cells(Rows.Count, "C").End(xlUp))
    .Value = Evaluate(Replace("IF(MID(@,2,1)="" "",MID(@,3,LEN(@)),@)", "@", .Address))
    .Replace " *", "", xlPart, , , , False, False
  End With
End Sub

Note: My code assumes the worksheet with your names is active when the code is run.

 

[tonywatsonhelp]

Thank you GWteB, for your help,
Rick, this looks like it will deal with the fact that the first name is not easy ti find as its not always the first word,
I'll need to test it out but looks amazing thanks very much.

Tony

 

[Rick Rothstein]

You are welcome. Note that I designed this for what you posted... single letter first initial to be ignored, but note that it will not work if that first initial has a period after it. If you could have such an occurrence, let me know and I'll modify the code to account for periods after first initials.