How to Extract First Name Middle Name & Surname separately

[P_Kale]

Hi,

How to Extract First Name Middle Name & Surname separately. For e.g

I have name in one column "Madhu Anant Thorat" but i want this to be divided and want to create 3 different columns like 

Madhu in NAME column, Anant in MIDDLE NAME column, THORAT in SURNAME column.

But if Name in column "RAJ DIXIT" then RAJ in NAME column and DIXIT in SURNAME column.

how i can handle this.

@marcus_sommer 

Thanks in advance.

[rwunderlich]

But if the Name has only two names, -1 will return the lastname and 2 will return the same name as MiddleName.

I usually normalize multiple spaces to a single space using MapSubstring like this:

SpaceMap:
Mapping Load Repeat(' ', RecNo()+1), ' '
AutoGenerate 10;

and then in data load

MapSubString('SpaceMap', Name) as Name

-Rob 

[P_Kale]

Hi @theoat I am good.

Thanks for your time once again.

I am using below given formula to extract middle name and it has worked for most of the cases, But not works for few names one of the e.g. you can see the below formula works in 1st case but not worked in 2nd case. I have checked the spaces between words as suggested by @N30fyte but in single space is there between Middle and Surname. 

SubField(PI_NAME_,' ',1) as PI_FirstName,

if(len(SubField(PI_NAME_,' ',3))>0, SubField(PI_NAME_,' ',2), '') as PI_MiddleName,

SubField(PI_NAME_,' ',-1) as PI_LastName,

& to concatenate First and Last Name

trim(SubField(PI_NAME_,' ',1)&' '&SubField(PI_NAME_,' ',-1)) as PI_FIRST_LAST_NAME,

Sr.no	PI_NAME	PI_FIRST_LAST_NAME
1	SURESH KUMAR CHAUDHARY	SURESH CHAUDHARY
2	TAPASH SUKLA DAS	TAPASH

Thanks in advance.

[P_Kale]

Hi @theoat have you get time to work on this.