Delimit text

[Manexcel]

I am using MSExcel 2016

I have a list of names (around 400+) in one column. The name consists of a Prefix1 / Prefix 2 / first name / last name / civil honour 1 / civil honour 2 / civil honour 3.

Some of the last names are hyphenated.

I am looking to delimit this text into separate columns. I have used the text to columns feature and it does appear to work.

But I would like to use formulas to achieve the delimit function.

An example of my data is below:

Mr Fred Smith
Mr John Jones OBE, BEM
Col Michael jones
The Rev Alex Jones
Col Sir John Adams OBE, TD, DL
Mr Jack Smith-Hamilton MBE
Dr Don Smith

etc.


Are there formulas that can assist to delimit the above?

I thank you for your time and understanding

 

[JoeMo]

Copy B1 across to cover the longest entry in your data (B1:H1 for your posted data). Then copy down (copy B1:H1 down to the last data entry in col A).

Excel Workbook
	A	B	C	D	E	F	G	H
1	Mr Fred Smith	Mr	Fred	Smith
2	Mr John Jones OBE, BEM	Mr	John	Jones	OBE	BEM
3	Col Michael jones	Col	Michael	jones
4	The Rev Alex Jones	The	Rev	Alex	Jones
5	Col Sir John Adams OBE, TD, DL	Col	Sir	John	Adams	OBE	TD	DL
6	Mr Jack Smith-Hamilton MBE	Mr	Jack	Smith-Hamilton	MBE
7	Dr Don Smith
Sheet2

 

[Manexcel]

Thanks so much for the quick response. The formula worked great. This can now be closed.

 

[JoeMo]

Thanks so much for the quick response. The formula worked great. This can now be closed.

You are welcome - thanks for the reply.