Split multiple names....

[Nicho]

Hi,

I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:

Belinda Smith & Grant Jones
Janine
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Jim Morris
Danielle & JohnSimpson

I would appreciate any advice.

Thanks,

Nicho

 

[jindon]

try

Function nicho(txt As String) As Variant
With CreateObject("VBScript.RegExp")
     .Pattern = "(\D+)\s&\s(\D+)\s(\D+)"
     If .test(txt) Then
          nicho = Array(.replace(txt, "$1 & $2"), .replace(txt,"$3"))
          Exit Function
     End If
     .Pattern = "Co(rp|mpany|-?op)?$"
     If .test(txt) Then
          nicho = Array(txt,"") 
          Exit Function
     End If
     .Pattern = "^(.+)\s(.+)\s&\s(.+)\s(.+)$"
     If .test(txt) Then
          nicho = Array(.replace(txt,"$1 $2 & $3"), .replace(txt,"$4"))
          Exit Function
     End If
     .Pattern = "&"
     If Not .test(txt) Then
          nicho = Split(txt)
          Exit Function
     End If
End With
End Function

 

[Nicho]

Hey jindon,

No difference....Bob Smith still in the first column.....