Data mixed up in different Columns

[Ali1586]

Hi, I have a spread sheet where my data is a bit mixed up. For example:

Column A Column B Column C Column D
John Joseph Smith 01/01/1986
Peter Black 15/01/1956



I basically need them all in a Forename/Middle/Surname/DOB order and if they don't have a middle name then that cell can be blank. HELP!?

 

[etaf]

will there only be 3 names at most , all with a space
Can you provide all possible examples you may have
Any Surnames with a double barrel name which has a space ?

Are they currently ALL in 1 column - Column A ?
Or randomly split across multiple columns

We can use a mixture of RIGHT() MID() Left() with a search for the space, but that will only work with the examples you have given so far - The layout/content is very important to getting the right solution

Although if the data is all in 1 column , then you can use DATA>Text to Columns and a Space as the delimiter

 

[Qadah]

Hi,

Attached is a file with formulas (custom one per column), that caters for different scenarios

formulas for each first column in fixed are:

Forename: =IF(ISTEXT(A3),A3,IF(ISTEXT(B3),B3,IF(ISTEXT(C3),C3,IF(ISTEXT(D3),D3,"No First Name"))))
Middle: =IF(B3=F3,IF(ISTEXT(C3),C3,IF(ISTEXT(D3),D3,"No Text Found")),IF(ISTEXT(B3),B3,"No Second Name"))
Surname: =IF(OR(C3=G3,C3=F3),IF(ISTEXT(D3),D3,""),IF(ISTEXT(C3),C3,""))
DoB: =TEXT(IF(ISNUMBER(A3),A3,IF(ISNUMBER(B3),B3,IF(ISNUMBER(C3),C3,IF(ISNUMBER(D3),D3,"No DoB Found")))),"dd/mm/yyyy")

Notes:
- In Surname, I am not adding any "No Last Name" note, you can change that and add it between the first set of "" in the formula
- You can remove the No First Name or No Second Name (or change them) by editing them (to remove, use "")
- if a date is not a date (like for Maya, day is 111, then you will get no DoB found
- I am using dd/mm/yyyy format, you might want to modify that depending your locale

 

[Peter_SSs]

Like this?

21 04 01.xlsm
	A	B	C	D	E	F	G	H	I
1
2	John	Joseph	Smith	1/01/1986		John	Joseph	Smith	1/01/1986
3	Peter	Black	15/01/1956			Peter		Black	15/01/1956
4	Ann		Jones	8/08/1976		Ann		Jones	8/08/1976
Fix columns

Cell Formulas
Range	Formula
F2:F4	F2	=A2
G2:G4	G2	=IF(ISNUMBER(C2),"",B2&"")
H2:H4	H2	=IF(ISNUMBER(C2),B2&"",C2)
I2:I4	I2	=IF(ISNUMBER(C2),C2,D2)

 

[Ali1586]

Thank you that's a big help

 

[Ali1586]

Like this?

21 04 01.xlsm
	A	B	C	D	E	F	G	H	I
1
2	John	Joseph	Smith	1/01/1986		John	Joseph	Smith	1/01/1986
3	Peter	Black	15/01/1956			Peter		Black	15/01/1956
4	Ann		Jones	8/08/1976		Ann		Jones	8/08/1976
Fix columns

Cell Formulas
Range	Formula
F2:F4	F2	=A2
G2:G4	G2	=IF(ISNUMBER(C2),"",B2&"")
H2:H4	H2	=IF(ISNUMBER(C2),B2&"",C2)
I2:I4	I2	=IF(ISNUMBER(C2),C2,D2)

Worked perfectly thank you!

 

[Peter_SSs]

You're welcome. Glad we could help.

BTW, Welcome to the MrExcel board!