Splitting Name field with non-standard data

[JasonKaufman]

I have name data that needs to be split into first and last name.
However, the data in this field is not standard.
Some data just has the first name, some is standard with first name, space, last name.
Others have standard with first name, space, last name with either a "/" with no space after last name followed by other data or
a space and then further data which would not want to show this additional data.

Any suggestions on creating a formula for this?

 

[SkipVought]

Hi,

Analyze your data and write a corresponding query to isolate certain data patterns that can then be parsed or corrected with a corresponding formula.

Lots of reiteration and work required. No way around it.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[BettyJ]

The following formulas may help you.

@FirstName
[tt]WhilePrintingRecords;
split({Sheet1_.Name}," ")[1];[/tt]

@LastName
[tt]WhilePrintingRecords;
if (" " in {Sheet1_.Name}) then
split({Sheet1_.Name}," ")[2]else
if ("/" in {Sheet1_.Name}) then
split({Sheet1_.Name},"/")[2];[/tt]

@LastnameWithoutAdditionalData
[tt]whileprintingrecords;
if "/" in {@LastName} then
split({@LastName},"/")[1]
else
{@LastName};[/tt]

@Name
[tt]whileprintingrecords;
@FirstName}& " "& {@LastnameWithoutAdditionalData};[/tt]

 

[BettyJ]

The following formulas may help you.

@FirstName[tt]
WhilePrintingRecords;
split({Sheet1_.Name}," ")[1];[/tt]

@LastName[tt]
WhilePrintingRecords;
if (" " in {Sheet1_.Name}) then
split({Sheet1_.Name}," ")[2]else
if ("/" in {Sheet1_.Name}) then
split({Sheet1_.Name},"/")[2];[/tt]

@LastnameWithoutAdditionalData
[tt]whileprintingrecords;
if "/" in {@LastName} then
split({@LastName},"/")[1]
else
{@LastName};[/tt]

@Name[tt]
whileprintingrecords;
{@FirstName}& " "& {@LastnameWithoutAdditionalData};[/tt]

 

[SkipVought]

Doing a Split on ALL your data may not work if your have first names that contain multiple words. Likewise with last names.

You first have to analyze your data and then categorize the data in such a way to group data with certain similarities that would enable you to use formulas like BettyJ suggested.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JasonKaufman]

Thanks to both of you for the help!
I shall test it out and see if Betty's formulas do the trick