person name columns help!

Easy search for the spaces...

=LEFT(B4,FIND(" ",B4,1)-1)

This will extract the Salutation, I would also search to see if this is MR, MRS etc as some of the names you provided didn't have one.

Not all carry salutations.

Originally Posted by shukla.ankur281190

Assume you have A2 with name

Try

B2

Formula:

Please Login or Register  to view this content.

Drag towards the column SALUTATION, First, Mid & Last name same formula

This gives me all first words, but some are not salutations.

Assuming you have names starting A2...

Salutation starting B2
=LEFT(A2,FIND(" ",A2,1)-1)

First Name starting C2
=MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,1)+1)

Surname starting D2
=MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)+1,50)

Originally Posted by milenia

Assuming you have names starting A2...

Salutation starting B2
=LEFT(A2,FIND(" ",A2,1)-1)

First Name starting C2
=MID(A2,FIND(" ",A2,1)+1,FIND(" ",A2,1)+1)

Surname starting D2
=MID(A2,FIND(" ",A2,FIND(" ",A2,1)+1)+1,50)

This is what I get.....Screenshot_4.jpg

Hi, you will need to do some Data Cleansing first, check for Salutation and how many spaces are present to confirm if there are middle names / initials.

Originally Posted by shukla.ankur281190

Ok Try

salutations B2

Formula:

Please Login or Register  to view this content.

Fiirst Name C2

Formula:

Please Login or Register  to view this content.

Mid Name D2

Formula:

Please Login or Register  to view this content.

Last Name E2

Formula:

Please Login or Register  to view this content.

This one shows, some other ones.
The only salutations that exist are MR, MRS, MISS, MS
WALTERS.jpg
MA, and MIMI are first names

you can also use the Text to Column function, that would separate the data the way you want it.

Originally Posted by FlameRetired

With an exhaustive list of salutations in H1:H4 this array entered formula in B1 filled down and across.

Formula:

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H
1	MR KEVIN SMITH	MR	KEVIN	SMITH				MR
2	MRS PATTY K SMITH	MRS	PATTY	K	SMITH			MRS
3	MISS DANA KRAUS	MISS	DANA	KRAUS				MISS
4	CLAUDIA SMITH		CLAUDIA	SMITH				MS
5	TERRY W SCALIA		TERRY	W	SCALIA
6	MS LYDIA H BROWN	MS	LYDIA	H	BROWN

It wasn't just salutations, sorry. It was all of the fields, there is a semi-working version above, but as I started to drag down the formula, i realize, that its a little more complicated because some letters start with M but are not salutations, and some last names carry 2 words, so the entire name may be between 2 words ie "lydia brown" to 5 words ie "mrs lydia fidia de brown" in which case
salutaion = mrs
first name = lydia
middle name = fidia
last name = de brown

Originally Posted by Jocote46

you can also use the Text to Column function, that would separate the data the way you want it.

What do you mean?