Separating text String with Name Date of birth and other information

[SeliM]

Thanks for taking time to read my enquiry.

I am trying to separate a text string output from a dataset that has a combined first name, middle Initial, family name, date of birth, hours and cost structure in one cell. The task is to separate into 6 columns.

I have been experimenting with substitute and textsplit which works fine when the delimiter is "-" and date of birth is using a "/". Example:
George E Smith-26/11/1948-26-1785 the formula result is correct across the 6 columns - first name, middle Initial, family name, date of birth, hours and cost

However, there are entries that use the following a "/" instead of "-" and a "-" instead of a "/". The formula of course separates the date of birth into 3 columns when we require one.

Text: George E Smith/26-11-1948/26/1787 is my challenge. I have tried using different methods for the delimiter but either the name doesn't separate as required or the DoB isn't correctly set out.

Your advice would be most welcomed.
Mel

 

[Peter_SSs]

I now have a variety of solutions that help with the various input styles in the data.

This sort of task is notoriously difficult to cover all cases. I was wondering whether you might ever have

1. Family names consisting of more than one word as in row 7 below?
2. Family or First names containing a hyphen?
3. Somebody who does not have a middle name and therefore no middle initial?

My suggested formulas below deal with (at least in some circumstances) points 1 & 3 above, but not point 2.

SeliM.xlsm
	A	B	C	D	E	F	G
1
2	George E Smith/26-11-1948/26/1787	George	E	Smith	26/11/1948	26	1787
3	George E Smith/26/11/1948/26/1787	George	E	Smith	26/11/1948	26	1787
4	George E Smith-26/11/1948-26-1785	George	E	Smith	26/11/1948	26	1785
5	George E Smith-26-11-1948-26-1785	George	E	Smith	26/11/1948	26	1785
6	George E Smith-26/11/1948-26/1785	George	E	Smith	26/11/1948	26	1785
7	Jon J van der Groot-26/11/1948-26/1785	Jon	J	van der Groot	26/11/1948	26	1785
8	Tim M Smith-Jones-26/11/1948-26/1785	Tim	M	Smith	Jones-26/11/1948	26	1785
9	George Smith-26-11-1948-26-1785	George		Smith	26/11/1948	26	1785
Split String (2)

Cell Formulas
Range	Formula
B2:B9	B2	=LEFT(A2,FIND(" "&IF(C2="","",C2&" "),A2)-1)
C2:C9	C2	=LET(t,TEXTSPLIT(A2," "),IFNA(INDEX(t,MATCH(1,LEN(t),0)),""))
D2:D9	D2	=REPLACE(LEFT(A2,MIN(FIND({"-","/"},A2&"-/"))-1),1,LEN(B2&C2)+2-(C2=""),"")
E2:E9	E2	=LET(s,REPLACE(LEFT(A2,LEN(A2)-LEN(F2&G2)-2),1,MIN(FIND({"-","/"},A2&"-/")),""),IFERROR(--s,s))
F2:G9	F2	=--TAKE(TEXTSPLIT(A2,{"-","/"}),,-2)
Dynamic array formulas.

 

[Peter_SSs]

Another option might be to utilise a user-defined function like this.

Function SPLITIT(s As String) As Variant
  Dim RX As Object, M As Object
  Dim a(1 To 6) As Variant
  Dim t As String
  Dim p As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "^.+?(?=\/|\-\d)"
  t = RX.Execute(s)(0)
  RX.Pattern = "\b[A-Z]\b"
  If RX.test(t) Then
    p = RX.Execute(s)(0).Firstindex + 1
    a(1) = Left(t, p - 2)
    a(2) = Mid(t, p, 1)
    a(3) = Mid(t, p + 2)
  Else
    a(1) = Split(t)(0)
    a(2) = vbNullString
    a(3) = Split(t, " ", 2)(1)
  End If
  
  RX.Pattern = "(\d{1,2})(\-|\/)(\d{1,2})(\-|\/)(\d{4})"
  Set M = RX.Execute(s)
  a(4) = DateSerial(M(0).Submatches(4), M(0).Submatches(2), M(0).Submatches(0))

  RX.Pattern = "(\/|\-)(\d+)(\/|\-)(\d+)$"
  Set M = RX.Execute(s)
  a(5) = Val(M(0).Submatches(1))
  a(6) = Val(M(0).Submatches(3))
  
  SPLITIT = a
End Function

In the sheet below, the function is entered in B2 and copied down.

SeliM.xlsm
	A	B	C	D	E	F	G
1
2	George E Smith/26-11-1948/26/1787	George	E	Smith	26/11/1948	26	1787
3	George E Smith/26/11/1948/26/1787	George	E	Smith	26/11/1948	26	1787
4	George E Smith-26/11/1948-26-1785	George	E	Smith	26/11/1948	26	1785
5	George E Smith-26-11-1948-26-1785	George	E	Smith	26/11/1948	26	1785
6	George E Smith-26/11/1948-26/1785	George	E	Smith	26/11/1948	26	1785
7	Jon J van der Groot-26/11/1948-26/1785	Jon	J	van der Groot	26/11/1948	26	1785
8	Bill De Bruyn-26/11/1948-26/1786	Bill		De Bruyn	26/11/1948	26	1786
9	Tim M Smith-Jones-26/11/1948-26/1785	Tim	M	Smith-Jones	26/11/1948	26	1785
10	George Smith-26-11-1948-26-1785	George		Smith	26/11/1948	26	1785
11	Jon-Boy Smith-Jones-26/11/1948-26/1785	Jon-Boy		Smith-Jones	26/11/1948	26	1785
Split String (3)

Cell Formulas
Range	Formula
B2:G11	B2	=SPLITIT(A2)
Dynamic array formulas.

 

[SeliM]

Thank You