Splitting Names

[AvinashBhawsar]

Hi I have been trying to split the name by using text to column and regex tool however, did not get the result.

here is the scenario-

i have a Name column which includes First, Middle and Last name.

requirement:- 

1) i want to split the name into 2 columns First & Last name.

2) First name column will be the combination of  First and Middle name.

3) Last Name column will only have Last name.

Problem :-:-some records in the Name Column do not have middle names, only first and last name. so, in this case first name should go into the first name column and last name into the last name column.

Sample Data:-

Names

A PRAVEEN KUMAR

RISHAB MOHAN

PALASH GAIKWAD

RAMESH ATRE

PREM MANJUNATH SHETTY

VINOD SHRINIVAS SHETTY

Output:-

First Name	Last Name
A PRAVEEN	KUMAR
RISHAB	MOHAN
PALASH	GAIKWAD
RAMESH	ATRE
PREM MANJUNATH	SHETTY
VINOD SHRINIVAS	SHETTY

Thanks,

[JosephSerpis · Accepted solution]

Hi @AvinashBhawsar I mocked up some regex which may tackle your challenge. The expression i'm using (.*)\s(.*)$ is searching for everything between a space which \s however the dollar sign is end of line. 

[AvinashBhawsar]

@JosephSerpis Thank you!

[AvinashBhawsar]

Hi Joseph,

i recently check those split names however, I found issues with some of the names which are not getting split in to first and last name column and also few are showing as blank in the first name column even though they have first name in the Name column. also there should b a logic to apply where we do not have last name we have to put . (dot) in the last name as it should not blank. can you please help me. @JosephSerpis 

Thanks,

Avinash Bhawsar

Last Name	First Name	NAME
		SWEETY
		SUMEET
		NISHANT
		MADHUSUDHAN
		ABHISHEK
		ADITYAJEET
		PALLAVI
		DIWAKAR
		BHAWNA
		KABILAN
		MANISHA
		JAYASHREE
		S.MOHANRANGA
		ROHIT
		VARUN
		NARENDER
		R.K.MADHUVANTHI
		LAKHVINDER
		NEETU
		MOAZZAM
		RUPAL
		VENKATESH
		V.S.KRUTHIKHA
		Himani.

[ChrisTX · Accepted solution]

For this requirement

 there should b a logic to apply where we do not have last name we have to put . (dot) in the last name as it should not blank.

Add a Formula tool before the RegEx tool.  Update the value for the Name column

IF FindString(Trim([Name]), " ") = -1

THEN [Name] + " ."
ELSE [Name]
ENDIF

You may need a Select tool before the Formula tool, to increase the field size.

 

Chris