How do I combine functions that will extract the first name into a column and the last name into a separate column


I'm lost when trying to separate the below name which are mixed up, some with spaces, some with commas.  I believe I need a IF statement with search (for a comma, even if there isn't one). 

Bob Carter Wilson, Will Johnson,Tom  Mike  Smith  Smith ,  Janie


I would tell you to upload a sample file showing potential variations. There might be an easy way to do this using text-to-columns depending on the exact data setup.
don (rep: 1835) Mar 26, '18 at 11:23 am
Add to Discussion



I'm afraid I don't dare write a formula for what you want to do. Below is a UDF (User Defined Function). Install it in a standard code module in the workbook where you have the names.

  1. From the worksheet, press Alt+F11 to open the VB editor
  2. Look for the name of your workbook in the Project Explorer Window on the left of the screen, select and right-click.
  3. In the dialog box that opens, select Insert and Module.
  4. Paste the below code in the blank space on the right of your screen.
Option Explicit
Function SplitName(Cell As Range, _
                   Optional Part As Integer) As String
    ' 26 Mar 2018
    ' If Part = 0 (or omitted) the first name will be returned
    ' If Part = 1 (or any other number) the last name will be returned
    Dim Tmp As String
    Dim Sp() As String
    Tmp = Trim(Cell.Value)
    If Len(Tmp) Then
        Sp = Split(Tmp, ",")
        If UBound(Sp) Then
            Tmp = Sp(1)
            Sp(1) = Sp(0)
            Sp(0) = Tmp
            Tmp = Join(Sp, " ")
        End If
        Sp = Split(Tmp)
        If Part Then
            SplitName = Sp(UBound(Sp))
            ReDim Preserve Sp(UBound(Sp) - 1)
            SplitName = Join(Sp, " ")
        End If
    End If
End Function

Now this function is availalbe anywhere in your workbook. You can use it like any other Excel function. And, like any other Excel function, it has a name and arguments. The name is "SplitName". It takes one required argument and one optional one (meaning you can omit it). The required argument is a cell address: the cell which contains the full name. The optional argument decides if you want the first or the last name. 0 (or omitted) returns the first name, 1 (or any other number) returns the last name.

So, if the joined name is in A2, use these two fomulas.

[B2] = SplitName(A2)
[C2] = SplitName(A2, 1)

The code considers all first names as "first name" and puts them into one cell. Only the last name as "last name". Once you are done you can replace the formulas with their values using Copy and Paste Special > Values and then perhaps do some final proof reading and editing.


Answer the Question

You must create an account to use the forum. Create an Account or Login