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

 

[Macropod]

Assuming your data are in A1, insert:
=SUBSTITUTE(IF(FIND("-",A1)<FIND("/",A1),SUBSTITUTE(A1,"-"," "),SUBSTITUTE(A1,"/"," ")),"-","/")
into B1. This will format the data consistently for parsing. Then insert:
=MID($B1,IF(COLUMN()=3,0,FIND(CHAR(1),SUBSTITUTE($B1&" "," ",CHAR(1),COLUMN()-3)))+1,(FIND(CHAR(1),SUBSTITUTE($B1&" "," ",CHAR(1),COLUMN()-2))-IF(COLUMN()=3,0,FIND(CHAR(1),SUBSTITUTE($B1&" "," ",CHAR(1),COLUMN()-3))))-1)
into C1 and copy across to H1.
Copy down as far as needed.

 

[SeliM]

Works wonderfully
Many thanks Macropod

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I
1	String	First	Middle	Family	Day	Month	Year	Cost	Hour
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
Sheet4

Cell Formulas
Range	Formula
B2:I3	B2	=TEXTSPLIT(A2,{" ","/","-"})
Dynamic array formulas.

 

[SeliM]

Thanks for your time, however we need the DoB as one string 26/11/1948.

 

[Macropod]

If you need the DOB as an actual date rather than just as a string, you could insert -- between = and MID (i.e. =--MID) in column F and format the cell as a date.

 

[Cubist]

How about

Book1
	A	B	C	D	E	F	G
1	String	First	Middle	Family	DOB	Cost	Hour
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
Sheet1

Cell Formulas
Range	Formula
B2:G3	B2	=SUBSTITUTE(TEXTSPLIT(IF(ISNUMBER(SEARCH("-",A2)),A2,SUBSTITUTE(SUBSTITUTE(A2,"/","-",2),"/","-",2)),{" ","/"}),"-","/")
Dynamic array formulas.

 

[Cubist]

Using LAMBDA.

Book1
	A	B	C	D	E	F	G
1	String	First	Middle	Family	DOB	Cost	Hour
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
Sheet1

Cell Formulas
Range	Formula
B2:G3	B2	=SUBSTITUTE(TEXTSPLIT(IF(ISNUMBER(SEARCH("-",A2)),A2,REDUCE(A2,{"-","-"},LAMBDA(a,b,SUBSTITUTE(a,"/",b,2)))),{" ","/"}),"-","/")
Dynamic array formulas.

 

[SeliM]

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

 

[Cubist]

No problem. I've missed a few cases. This should cover all

Book1
	A	B	C	D	E	F	G
1	String	First	Middle	Family	DOB	Cost	Hour
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
Sheet1

Cell Formulas
Range	Formula
B2:G6	B2	=LET(s,SUBSTITUTE(A2,"-","/"),t,SUBSTITUTE(TEXTSPLIT(REDUCE(s,{"-","-"},LAMBDA(a,b,SUBSTITUTE(a,"/",b,2))),{" ","/"}),"-","/"),IFERROR(--t,t))
Dynamic array formulas.