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 , JanieI'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 , JanieI'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.
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))
Else
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.